Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Solution: Specify Values in Data

Explore how to specify valid column values by creating and managing lookup tables in SQL. Learn to enforce data integrity with foreign key constraints, update or add values without downtime, and handle obsolete entries effectively. Understand why lookup tables offer more flexibility and portability compared to ENUM types or check constraints.

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