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.
