What is SQL ENUM? - Scaler Topics

While creating tables, or entering data in a table that has some repeated entries like the size of a shirt (Large, Medium, Small, etc), or seat type in a movie theater, have you ever thought that it would be really helpful if there was something to limit the entries to only those specific values, or something that would make your task easier by shortening the number of characters you need to type to do the same repeated task? If YES, then ENUM is something that will exactly meet your expectations!

In SQL, the ENUM data type is a string object that allows us to limit the entries in a particular column by permitting only the String values specified for that column during the table creation. ENUM is the short form for the term “Enumeration”.

The values are represented using numeric indices like 1 for the first value, 2 for the second value, and so on.

Note: One can compare this to the Array data structure, the only differences are:

we use ENUMs only for strings, and
this follows 1-based indexing.

Syntax

The following is the syntax to be used for using the ENUM data type.

Note: All codes in this article are written in reference to MySQL.

In the above syntax, we have declared ENUM for Column2 and the values for the ENUM are 'value_1','value_2','value_3', we can add more values if we need.

Example of SQL ENUM

Let us create a table TICKET_DETAILS containing details of tickets for a movie. There will be three columns, one for the Seat Number (this will serve as the key), Person's Name, and Ticket Class. We will be using ENUM for the Ticket Classes.

Code:

The Ticket_Class column accepts only 3 values that are mentioned during the creation of the table.

Inserting SQL ENUM Values

The best thing about using ENUM is that we do not need to write the entire word, again and again, rather we can use the indices to represent them.

Note: Writing the entire word is not wrong, but it is not convenient.

Let us insert a few entries to our TICKET_DETAILS table.

Code:

Let us now use the SELECT statement and view the table we just created and entered data:

Code:

Output: Inserting SQL ENUM Values

Filtering SQL ENUM Values

We can filter out the needed data from the table using the WHERE clause and the ENUM indices or the String itself. Let us take an example to filter out data of the person with Ticket_Class “Royale”.

Code:

OR,

Output:

Filtering SQL ENUM values

As we can see, both the String value and the index can be used interchangeably.

Sorting MySQL ENUM Values

In MySQL, the sorting of the ENUM values is done by comparing the indices of the values we have assigned during the creation of the table. A higher index means greater value and a lower index means lower value.

In case, there is NULL value, it comes before any other value. We use the ORDER BY clause for sorting the data.

Let us take an example where we get all the data from the TICKET_DETAILS table in the order of the highest pricing ticket first. Royale is the most expensive ticket and Club is the least expensive one.

Code:

Output:

Sorting MySQL ENUM values

As we expected, the Royale class comes first and the Club class comes at the very end, this happens because during the creation of the table Royale had an index of 3 and Club had an index of 1, and here we are doing the sorting in decreasing order.

Advantages and Disadvantages of SQL ENUM Data Type

Let us talk about the good things first, the advantages of the SQL ENUM data type are as follows:

As numeric indices are being used to store the String values, so this is a compact data storage.
ENUMs makes the queries short by using the numeric indices instead of the Strings and thereby making the queries and the outputs readable.

Now, let us talk about the disadvantages:

If we want to make any change in the values of the ENUMs, this might include spelling change or ordering change, then we need to create an entirely new table using the ALTER TABLE statement and this is pretty much time and resource consuming operation.
ENUMs is not available on every Database Management System, so, porting to other RDBMS can create an issue.
No function can be used with ENUMs members, like CONCAT(), ASCII(), LCASE(), or any other string function.
It is recommended not to use numeric values in ENUMs as this might create confusion with the indices. We cannot add more attributes to the ENUMs list.

Learn more

Follow our related article links to know more about SQL

Conclusion

ENUM data type is a string object that is used to represent String values using numeric indices, one can think of this as a one-based indexing array of strings.
ENUMs make query writing clean and efficient, we can represent the entire string using numeric indices and get the desired output.
ENUMs are not available in every RDBMS, so porting can be an issue.
Making changes to the ENUM needs to be addressed by creating an entirely new table using the ALTER TABLE statement.