devxlogo

Using ENUM in MySQL Columns

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;

devx-admin

Share the Post: