Introduction to the Enum Data Type in SQL

Posted on by Ian

The Enum (enumeration) data type in SQL is a specialised string object that allows us to define a list of predefined values for a column. This data type can be particularly useful when we want to restrict the possible values that can be stored in a specific column to a fixed set of options.

Basic Concept

An Enum column can contain one value from a predefined list of allowed values. This list is specified when creating the table or altering the column. For example, you might use an Enum to represent:

  • Days of the week
  • Sizes (small, medium, large)
  • Status types (active, inactive, pending)

Syntax

The basic syntax for creating an Enum column goes something like this:

column_name ENUM('value1', 'value2', 'value3', ...)

At least, that’s how it goes in MySQL and MariaDB.

Depending on your DBMS, it may also be possible to include optional arguments for character set and collation:

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

In PostgreSQL we need to create the type first. So it goes something like this: 

CREATE TYPE enum_name AS ENUM ('value1', 'value2', 'value3', ...);

Then the column can use that type, which will restrict the input values to those listed in the type.

Example

Let’s create a simple table in MySQL to demonstrate the use of Enum:

CREATE TABLE IF NOT EXISTS shirts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    size ENUM('small', 'medium', 'large')
);

In this example, the size column can only contain the values small, medium, or large.

Testing the Enum Column

Let’s try inserting data to test the Enum column.

First, let’s insert some rows that contain obviously valid data:

INSERT INTO shirts (id, name, size) VALUES
(1, 'Red T-Shirt', 'small'),
(2, 'Blue Polo', 'medium'),
(3, 'Green Sweater', 'large'),
(4, 'Yellow Tank Top', 'small'),
(5, 'Black Hoodie', 'large');

Result:

Records: 5  Duplicates: 0  Warnings: 0

All rows were inserted successfully. We can see that all our values for the Enum column are in the list of values that we provided when we created the column.

Let’s insert the correct value, but wrong case:

INSERT INTO shirts (id, name, size) VALUES
(6, 'Purple Shirt', 'MEDIUM');

Result:

Query OK, 1 row affected (0.00 sec)

So far, so good.

Now let’s try a NULL value:

INSERT INTO shirts (id, name, size) VALUES
(7, 'White Undershirt', NULL);

Result:

Query OK, 1 row affected (0.01 sec)

This was fine too, because we didn’t put any NOT NULL constraints on the column.

Let’s test it with an empty string:

INSERT INTO shirts (id, name, size) VALUES
(8, 'Gray Vest', '');

Result:

ERROR 1265 (01000): Data truncated for column 'size' at row 1

We got an error in this case, but this may depend on the DBMS.

Now let’s try inserting an obviously invalid value:

INSERT INTO shirts (id, name, size) VALUES (9, 'Orange Shirt', 'extra-large');

Result:

ERROR 1265 (01000): Data truncated for column 'size' at row 1

Same error. We got this error because extra-large is not in the list for the Enum column.

Advantages of Using Enum

  1. Data Integrity: Enums ensure that only predefined values can be inserted into the column.
  2. Space Efficiency: Enums are stored as integers internally, making them more space-efficient than VARCHAR for fixed sets of strings.
  3. Readability: Enum columns make the schema more self-documenting, as the allowed values are part of the column definition.

Considerations and Limitations

  1. Database Portability: Not all database systems support Enum. It’s supported in MySQL and MariaDB, but not in SQL Server or Oracle. Also, PostgreSQL supports the Enum type, but we have to create it first, using the CREATE TYPE statement.
  2. Inflexibility: Adding or removing values from an Enum list requires altering the table structure, which can be problematic in production environments.
  3. Sorting: The sort order of Enum values is based on their internal integer representations, which might not always match alphabetical order.

Alternatives to Enum

If your database doesn’t support Enum or you need more flexibility, consider these alternatives:

  1. Check Constraints: Use a VARCHAR column with a CHECK constraint to limit allowed values.
  2. Foreign Key: Create a separate table for the allowed values and use a foreign key relationship.

Conclusion

The Enum data type can be a handy tool for maintaining data integrity and improving database efficiency when dealing with columns that have a fixed set of possible values. However, it’s important to consider the specific requirements of your application and the limitations of Enum before implementing it in your database schemas.