Enum, Lookup Table or Check Constraint? Modelling a Fixed Set of Values

Order status, account type, risk tier, KYC state: every schema has a handful of columns that can only hold values from a fixed, controlled set. How you store that set decides whether adding a value next quarter is a one-line insert or a table-rewriting migration you have to schedule around a maintenance window.

PostgreSQL gives you three honest ways to model a controlled vocabulary: a native ENUM type, a lookup table joined by a foreign key, or a plain text column guarded by a CHECK constraint. They all enforce the same thing on day one — only approved values get in. They diverge sharply on the day the set changes, and the set always changes. This is a comparison on the axes that actually cost you money later: adding and removing values, referential integrity, join cost, and reporting.

The three options, side by side

Here is the same fixed set — order statuses — modelled all three ways in PostgreSQL 17, with the cost of adding a value written next to each.

-- OPTION 1: native ENUM type
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders_enum (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

-- Adding a value is DDL. Inside a transaction block the new value
-- cannot be used until AFTER the transaction commits, so you cannot
-- add 'refunded' and backfill rows to it in the same migration.
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';
-- Removing a value: there is NO ALTER TYPE ... DROP VALUE.
-- You must recreate the type and rewrite every column that uses it.


-- OPTION 2: lookup table + foreign key
CREATE TABLE order_status (
    code       text PRIMARY KEY,
    label      text NOT NULL,
    sort_order int  NOT NULL,
    is_active  boolean NOT NULL DEFAULT true
);

INSERT INTO order_status (code, label, sort_order) VALUES
    ('pending',   'Pending',   10),
    ('paid',      'Paid',      20),
    ('shipped',   'Shipped',   30),
    ('cancelled', 'Cancelled', 40);

CREATE TABLE orders_fk (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL DEFAULT 'pending' REFERENCES order_status (code)
);

-- Adding a value is one row of DML. No DDL, no lock on orders_fk.
INSERT INTO order_status (code, label, sort_order) VALUES ('refunded', 'Refunded', 50);
-- Retiring a value keeps referential history intact.
UPDATE order_status SET is_active = false WHERE code = 'cancelled';


-- OPTION 3: text column + CHECK constraint
CREATE TABLE orders_chk (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL DEFAULT 'pending'
        CONSTRAINT orders_status_chk
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

-- Adding a value means dropping and re-adding the constraint.
-- NOT VALID skips the full-table scan; VALIDATE then runs under a lighter lock.
ALTER TABLE orders_chk DROP CONSTRAINT orders_status_chk;
ALTER TABLE orders_chk ADD CONSTRAINT orders_status_chk
    CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', 'refunded')) NOT VALID;
ALTER TABLE orders_chk VALIDATE CONSTRAINT orders_status_chk;

The cost that decides it: changing the set

Adding a value to a lookup table is an INSERT. It touches one small table, takes no lock on the tables that reference it, and needs no schema migration at all — in most shops it is a data change an application can make at runtime. Removing a value is a soft delete: flip is_active to false and the historical rows that still point at it keep their referential integrity, because the foreign key stops you deleting a code that is still in use. That is the behaviour you want.

Adding a value to a CHECK constraint is DDL. You drop and re-add the constraint, and re-adding a plain CHECK scans every existing row to validate it — on a large table that is a full-table scan under lock. The NOT VALID then VALIDATE CONSTRAINT two-step avoids the blocking scan, but you have to know to reach for it, and the vocabulary is now duplicated in the constraint definition of every table that uses it. Change the set and you edit each one.

Adding a value to an ENUM looks cheap — one ALTER TYPE ... ADD VALUE — and it is, until the restrictions bite, which is the next section. A schema change that forces a table rewrite or a blocking lock is exactly the kind of high-friction deployment that pushes up your change-failure rate and lead time, so the modelling choice is a delivery-metrics choice as much as a schema one.

Where the enum bites back

The native enum is the option people reach for first and regret third. Two documented behaviours cause most of the pain.

You cannot remove a value. There is no ALTER TYPE ... DROP VALUE. If you added refunded by mistake, or a status is genuinely retired, the only supported route is to create a new type, alter every column to the new type, and drop the old one — a rewrite of every table that uses it. On a busy production database that is a scheduled, locked operation, not a Tuesday-afternoon change.

The new value is unusable in the same transaction. The PostgreSQL manual is explicit: if ALTER TYPE ... ADD VALUE runs inside a transaction block, the new value cannot be used until after that transaction commits. Most migration frameworks wrap each migration in a transaction, so the natural migration — add refunded, then backfill some rows to it — fails, and you have to split it across two separately-committed migrations. It is a foot-gun that only reveals itself in the deploy that matters.

Enums are not without merit. They are compact on disk, they need no join, they are self-documenting in the schema, and — unlike the other two — they carry an explicit sort order, so ORDER BY status follows the declared sequence rather than alphabetising your workflow. ADD VALUE ... BEFORE / AFTER lets you slot a new value into the right position, and RENAME VALUE handles relabelling. If your set is small, genuinely stable, and its order carries meaning — think severity levels or a rating scale — an enum is a reasonable, tidy choice.

The check constraint’s narrow niche

A text column with a CHECK is the lightest option: no extra type, no extra table, no join, and the allowed set is legible right there in \d orders. For a truly tiny, near-immutable set used in exactly one table — a two-value flag, a fixed pair of directions — it is the pragmatic answer, and the constraint reads as documentation.

It falls apart the moment the vocabulary is shared. The set lives inside the constraint definition, so using the same list across five tables means five constraints to keep in step, and there is no canonical place to attach a human-readable label, a display order, or an active flag. There is also nothing to join to when a report needs to turn shipped into “Shipped” for a customer-facing screen. A check constraint enforces a set; it does not describe one.

Why the lookup table is the default

Make the lookup-table-plus-foreign-key pattern your default and reach for the other two only with a reason. It wins on every axis that hurts later. Changing the set is DML, not DDL, so it does not queue behind a migration or a lock. Referential integrity is enforced by the foreign key, exactly as it is for any other relationship, and the same key stops you retiring a code that rows still depend on. The table is the natural home for metadata — the display label, a sort column that recovers the one thing enums do for free, an is_active flag, effective dates, a description — none of which a check or an enum can hold.

The usual objection is join cost. In practice it is noise: the lookup table is a handful of rows, it lives in cache permanently, and the planner resolves it with a hash join you will struggle to measure. If you genuinely want to avoid the join on a hot reporting path, store the code as text in the main table — as the examples above do — with the foreign key pointing at the lookup. Reports and dashboards read the code directly with no join at all, and you only touch the lookup table when you need the label or the metadata. You get integrity and free reads at once.

Two practices make the pattern robust. Seed the lookup rows in a versioned migration alongside the table, not by hand in production, so the controlled set is reviewed and reproducible; wiring that seed into a migration step your CI actually enforces keeps environments honest. And where the column is really a lifecycle — an order or payment moving through states — remember the lookup table only validates membership, not legal transitions; enforcing that refunded can only follow paid is application logic, the same discipline that makes an operation like an idempotent, safely retryable write behave under load.

Reach for an enum when the set is small, fixed, and ordered and you value the on-disk compactness. Reach for a check constraint when the set is trivial and confined to one table. For everything else — which is most controlled vocabularies in a real schema — the lookup table is the choice you will not have to unpick when the business adds a status you did not foresee.