SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Solution: Specify Values in Data

Solution: Specify Values in Data

Let's solve the antipattern 31 Flavors by specifying values in the data.

We'll cover the following...

There’s a better solution to restrict values in a column: creating a lookup table with one row for each value that we allow in the Bugs.status column and then declaring a foreign key constraint on Bugs.status to reference the new table.

MySQL
CREATE TABLE BugStatus (
  status VARCHAR(20) PRIMARY KEY
);
INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
CREATE TABLE Bugs (
  -- other columns
  status VARCHAR(20),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
  ON UPDATE CASCADE
);

When we insert or update a row in the Bugs table, we must use a status value that exists in the BugStatus table. Although this enforces the status values like ENUM or a check constraint, there are also several ways this solution offers more flexibility.

Querying the set of values

The set of permitted values is now stored ...