SQL Database Design » 8. User-Defined Types » 8.3 Enumerated Types

8.3 Enumerated Types

Table of Contents

The PostgreSQL manual defines enumerated types (or enumerations/enums for short) as data types that comprise a static, ordered set of values. Enumerated types are created using the ENUM keyword. Note, that I have only seen ENUMs use string data, so this could be considered another (weak) property of enums. Conceptually, an enumeration represents an ordered "one out of many" selection, much like what you would get from a dropdown box on a graphical user interface. Enumerations are for ordinally-scaled attributes only.

It is important to note, that, by nature, enumerated types contradict the relational model. The relational model inherently deals with sets only and does that pretty well (fast). If your only concern is to restrict a column to a number of predefined values without the need for an order, you should prefer traditional check constraints using the IN operator – if supported. Note however, the IN operator does not qualify as enumerated type. They are indeed similar, but the IN operator uses set logic and is for nominally-scaled attributes.

Please refer to the section 10.4 Check Constraints for examples on how to create set-like constrained types. It is only for MySQL, which does not support check constraints, that you might want to use ENUM instead. On MySQL, ENUM is the only type to constrain columns to predefined values (aside from a regular SQL table). Please also refer to the maximum list issue in 10.4.3 Maximum List Size, which also applies to ENUMs.

Enumerations can be created in three different ways:

  • Sequence tables (portable, reusable)
  • PostgreSQL-style ENUMs (non-portable, reusable)
  • MySQL-style ENUMs (non-portable, non-reusable)

Sequence tables are discussed in the section 12.2 Sequence Tables. The two others are discussed in this section. I have also prepared a decision making heuristic for predefined value implementations in the section 13.3 Predefined Values.

In my beginnings, I used to naively declare ENUM columns all over my designs, just to save a few tables, foreign keys, and joins. Later, I ran into problems with object-relational mapping tools that did not support ENUMs properly and started to research. I then realized, that ENUM is not a standard SQL keyword and thus is not portable. The ENUM keyword is only available on PostgreSQL 8.3+ and MySQL 5.x (of the DBMSs I have examined).

8.3.1 PostgreSQL ENUMs

On PostgreSQL, you specify enums much like domains (see section 8.1 Domains):

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')
CREATE TABLE Persons
(
    ...
    current_mood mood,
    ...
)

This is the ideal form of an enumerated type. Due to its user-defined nature, it can be reused. While you might take that for granted, this is not the case on MySQL, as explained later. PostgreSQL ENUMs allow comparison operators to be used:

SELECT * FROM Persons WHERE current_mood > 'sad'

In general, PostgreSQL ENUMs are separate from others ENUMs and are not comparable (SQL error), even if they share a number of values. PostgreSQL only allows you to do so by casting the ENUM values after accessing the enum values via a proprietary :: operator. You can read the full story in the PostgreSQL manual.

8.3.2 MySQL ENUMs

MySQL only allows inline, non-reusable ENUMs to be declared:

CREATE TABLE Persons
(
  ...
  current_mood ENUM ('sad', 'ok', 'happy'),
  ...
)

MySQL does not allow comparison of the values themselves:

SELECT * FROM Persons WHERE current_mood > 'sad'

Instead, you have to use the numeric enum value behind it:

SELECT * FROM Persons WHERE current_mood > 1

Note, that, as with the AUTO_INCREMENT default, ENUM starts counting at one.

I have not done any performance testing, but some people claim, that MySQL ENUMs are much faster than separate tables, especially on large databases. Furthermore, enums can give you some savings in database size, depending on the implementation. For example, a MySQL ENUM occupies only one byte for up to 255 values and two bytes for up to 65535 values.

MySQL ENUMs have serious downsides. I have dedicated an extra section to one specific problem in the section 10.4.2 MySQL ENUM Propagation Workaround. However, there is a much more serious problem on MySQL when it comes to changing the list of values. According to this newsgroups thread, MySQL's ALTER TABLE implementation is inferior with regards to changing ENUMs: MySQL's ALTER TABLE always creates a full copy of the table, even if it is not necessary.

Adding a value to the end of an ENUM should be uncritical. Even then the ALTER TABLE mimic code is executed and an unmodified copy of the table including all data and indices is created. Consequently, you should pay extra attention to the nature of your ENUM data on MySQL. Only use ENUM if the values rarely need to be updated, or better, not at all.

8.3.3 Other DBMSs

Even though Oracle and SQL Server each offer a proprietary CREATE TYPE syntax to declare user-defined types, they do not support the ENUM keyword. You either have to implement sequence tables or fall back to check constraints using the IN operator – of course, only if your data is nominally scaled.

References for Enumerated Types:

Last updated: 2010-11-10