SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Synopsis: 31 Flavors

Synopsis: 31 Flavors

Let’s learn what happens if we restrict a column to use specific values.

We'll cover the following...

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.

MySQL
CREATE TABLE PersonalContacts (
  -- other columns
  salutation VARCHAR(4)
  CHECK (salutation IN ('Mr.', 'Mrs.', 'Ms.', 'Dr.', 'Rev.')),
);

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 ...