ENUM in MySQL

ENUM:

•           It is a String Object in MySQL.
•          The values are chosen from the list of values which are defined at the time of creation of table.

-           

        If you consider tables below, User table is referencing User_Status table with User_Status column. In this case, we have to have a separate Status table.

       This approach will be same in all the tables where we have small master tables either for Status or flags or any such kinds of tables.

 Instead of the above approach, we can define the User_Status column with the ENUM and define the values in that column for the table. This way we can avoid creating small master tables.

We can even implement the data integrity through this by restricting the end user to choose the values from the list of defined set of values.

Script to create table with ENUM data type-

Script to insert values into the table –

Data will look as below-

In DML’s , we can use either the index of the values or the defined values in the column.

For example- If we want to update the User_id -3 record status from Blocked to Active, we can use the following command – 

Any one of the above approach would be fine.

But, when we query the table, we will see the character value in the column. 

Altering ENUM in table:

If we want to remove one of the values (blocked) from the list in ENUM, we can use the following command – 

Let’s describe and see whether it has removed it or not.

Describe Users_Enum_Demo;

If we want to change the list of values in ENUM, Ex: lets change Deleted to Delete in the list of values in ENUM,

Describe Users_Enum_Demo;

You can see in the list of values Deleted is changed to Delete.

Please leave your comments in comments.