RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Dec 21, 2018

Using ENUM in MySQL Columns

ENUMs are very powerful definitions and they help attain data validation when the values are pre-defined. With this you can avoid application-level validation or the same can be extracted from the database and used in applications to define the validation data.

In the DDL below, we are defining a column named transport_type with its values defined in ENUM. This will ensure that the values are always validated by the database before any insertion or updation.

CREATE TABLE TransportTypes (
transport_type_id INT,
transport_type ENUM('Bus','Train','Flight')

The following is valid SQL:

-- -- -- -- -- -- -- -- -- -- -- --
INSERT INTO TransportTypes VALUES (1,'Bus'), (2,'Train'), (3,'Flight');

Below is an invalid SQL query. This results in error during the execution:
-- -- -- -- -- -- -- -- -- -- -- --
UPDATE TransportTypes set transport_type ='Train2' where transport_type_id=2;
Sridhar M S
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date