SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Antipattern: Specify Values in the Column Definition

Antipattern: Specify Values in the Column Definition

Let's explore the insights we can derive from the antipattern 31 Flavors.

We'll cover the following...

Many people choose to specify the valid data values when they define a column. The column definition is part of the metadata, which is the definition of the table structure itself.

For example, we could define a CHECK constraint on the column. This constraint disallows any insert or update that would make the constraint false.

Press + to interact
CREATE TABLE Bugs (
   -- other columns 
   status VARCHAR(20) 
   CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED')) 
);

MySQL supports a non-standard data type called ENUM that restricts the column to a specific set of values.

Press + to interact
CREATE TABLE Bugs (
   -- other columns
   status ENUM('NEW', 'IN PROGRESS', 'FIXED'),
);

In MySQL’s implementation, we declare the values as strings, but internally the column is stored as the ordinal number of the string in the enumerated list. The storage is therefore compact, but when we sort a query by this column, the result is ordered by the ordinal value, not alphabetically by the string value. This may be unexpected behavior for us.

Other solutions include domains and user-defined types (UDTs). We can use these to restrict a column to a specific set of values and conveniently apply the same domain or data type to several columns within our database. But these features are not supported widely among brands of RDBMSs yet.

Finally, we could write a trigger that contains the set of permitted values and causes an error to be raised unless the status matches one of ...