Should you use an ENUM type, a CHECK constraint, or a lookup table for a fixed set of values?
A CHECK on text is the easiest to change, an ENUM is compact and ordered but awkward to alter, and a lookup table with a foreign key is best when the values need extra data or change often.
Explain it simply
Say an order can be placed, shipped or delivered. You have three ways to stop someone saving shiped.
An ENUM is a tiny built-in type: stored in 4 bytes, sorts in the order you declared. A CHECK constraint keeps the column as plain text and rejects values outside a list. A lookup table makes statuses real rows that other tables point at with a foreign key.
Example
-- 1. ENUM
CREATE TYPE order_status AS ENUM ('placed','shipped','delivered');
-- 2. CHECK
CREATE TABLE orders_b (
status text CHECK (status IN ('placed','shipped','delivered'))
);
-- 3. Lookup table
CREATE TABLE statuses (code text PRIMARY KEY, label text NOT NULL);
CREATE TABLE orders_c (
status text REFERENCES statuses (code)
);
ALTER TYPE order_status ADD VALUE 'cancelled'; -- adding is easy
-- removing or renaming a value is notWhat to say in the interview
"For a short, stable list I use a CHECK constraint for flexibility, or an ENUM when I want compact storage and ordering. If the values carry labels or flags, or the business edits them, I use a lookup table with a foreign key. ENUM values can be added but not dropped easily."
Common mistakes
- Choosing ENUM for a list that changes monthly.