Synopsis: 31 Flavors

In a personal contact information table, the salutation is a good example of a column with only a few values. Once we support Mr., Mrs., Ms., Dr., and Rev., we’ve accounted for virtually everyone. We could specify this list in the column definition, using a data type or a constraint, so that no one can accidentally enter an invalid string in the salutation column.

That should settle it, there are no other salutations to support, right? Let’s consider the following scenario.

One day, your boss tells you that your company is opening a subsidiary in France, and that you need to support the salutations M., Mme., and Mlle in addition to the ones already listed. Your mission is to alter your contact table to permit these values. This is a delicate job and may not be possible without interrupting the availability of that table.

You also thought your boss mentioned that the company is trying to open an office next month in Brazil.

Objective: Restrict a column to specific values#

Restricting a column’s values to a fixed set of values is very useful. Ensuring that a column never contains an invalid entry can significantly simplify its use. For example, in the Bugs table of our example database, the status column indicates whether a given bug is NEW, IN PROGRESS, FIXED, and so on. The significance of each of these status values depends on how we manage bugs in our project, but the point is that the data in the column must only contain these values.

Ideally, we need the database to reject invalid data.

Let’s see what happens when we press RUN in the following playground.

In the above playground, we have tried to insert invalid data in the Bugs table, but as we are using the status column of the BugStatus table and we don’t have any entry there, the database is not letting us insert the data. This happens because we have restricted the table to use specific values. We will study this in detail in the next lesson.

Legitimate uses of the antipattern#

ENUM is a non-standard data type supported by MySQL that restricts the column to a specific set of values. ENUM may be a good choice to use if the set of values is unchanging. It’s still difficult to query the metadata for the set of values, but we can maintain a matching list of values in application code without getting out of sync.

An ENUM is most likely to succeed when it would make no sense to alter the set of permitted values, such as when a column represents an either/or choice with two mutually exclusive values, like LEFT/RIGHT, ACTIVE/INACTIVE, ON/OFF, INTERNAL/EXTERNAL, and so on.

Check constraints can be used in many ways other than simply to implement an ENUM-like mechanism, such as checking that a time interval’s start is less than its end. We will be discussing this in detail in the next lesson.