dcsimg
 

Using ENUM in MySQL Columns

Friday Dec 21st 2018 by Sridhar M S

Language: SQL, Expertise: Intermediate - See how ENUMs are very powerful definitions and how they help attain data validation when the values are pre-defined.

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;
Home
Mobile Site | Full Site