A few months ago, in You Can Point a Foreign Key Where?!, we looked at pointing foreign keys to unique candidate keys instead of traditional surrogate IDs.
In the comments, Tom offered an essential reminder from decades in the trenches:
"You and I have decades now of CREATE TABLE... and those decades have shown us the only thing that is truly immutable are the things stakeholders tell us are absolutely not immutable... business codes have a way of becoming less immutable across years. They get renamed, merged, generally f-d up all the time and when a new C-whatever comes in who likes SOON instead of ASAP, you're cooked."
Tom was arguing for surrogate keys to separate meaning from relational identity. He was right. Business vocabulary is fluid. What feels permanent during sprint planning rarely stays permanent across three fiscal years.
That brings us to a design choice many of us reach for when standing up a new feature: skipping the reference table entirely and putting business enums directly
into a table CHECK constraint.
CREATE TABLE orders (
order_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
order_date DATE NOT NULL,
order_status VARCHAR2(20) NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (order_id),
CONSTRAINT ck_orders_status
CHECK (order_status IN ('OPEN', 'PENDING', 'SHIPPED', 'CANCELLED'))
);To be clear: this is not a post against CHECK constraints. CHECK constraints are indispensable. This is a post about which rules they
can actually hold.
Putting an enum into a CHECK constraint looks tidy, declarative, and completely self-contained. It avoids spinning up another table, mapping
another entity, or coordinating extra seed files. In the moment, it feels like disciplined engineering.
Until the business evolves, and the guardrail quietly stops guarding.
The Silent Failure: Enforcing Neither
The standard objection to putting enums in CHECK constraints is migration friction. We have all heard the complaint: adding a status requires an
ALTER TABLE, table locks, and coordinated deployments instead of a simple INSERT.
That argument is true, but it is an ergonomic argument. A determined engineering team can easily wave it off as an acceptable deployment cost.
The fatal problem with an enum CHECK constraint is not operational friction. It is structural failure.
Consider Tom's scenario: leadership decides that 'PENDING' is too vague. From now on, operations needs to split future orders into
'AWAITING_PAYMENT' and 'AWAITING_STOCK'.
What happens to existing records? The business does not want to rewrite history. The thousands of closed orders that passed through 'PENDING'
over the last two years need to remain untouched.
Now look at what happens to your constraint:
ALTER TABLE orders DROP CONSTRAINT ck_orders_status;
ALTER TABLE orders ADD CONSTRAINT ck_orders_status
CHECK (order_status IN (
'OPEN',
'PENDING',
'AWAITING_PAYMENT',
'AWAITING_STOCK',
'SHIPPED',
'CANCELLED'
));Because a table constraint evaluates every row equally across the entire table, 'PENDING' must remain in the allowed list forever just to
keep historical rows valid.
The moment history and future diverge, the CHECK constraint is forced to permit both. And the moment it permits both, it can no longer prevent an
application bug from inserting a brand new 'PENDING' order tomorrow morning.
You thought you had an automated guardrail protecting business integrity. What you actually have is a decorative plaque. The constraint that was written to enforce valid state transitions has quietly stopped constraining.
Temporal Boundaries Trump Hardcoded Strings
When we move business vocabulary out of DDL and into a proper reference table, we don't resort to a lazy is_active boolean flag either. As we
saw in The Boolean is Lying to You, booleans erase history.
Instead, we use explicit temporal boundaries:
CREATE TABLE order_statuses (
status_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
status_code VARCHAR2(20) NOT NULL,
description VARCHAR2(100) NOT NULL,
display_seq NUMBER NOT NULL,
effective_start_date TIMESTAMP WITH TIME ZONE NOT NULL,
effective_end_date TIMESTAMP WITH TIME ZONE,
--
CONSTRAINT pk_order_statuses PRIMARY KEY (status_id),
CONSTRAINT uq_order_statuses_code UNIQUE (status_code),
CONSTRAINT ck_order_statuses_dates
CHECK (effective_end_date IS NULL OR effective_end_date >= effective_start_date)
);(Notice where the CHECK constraint lives: validating that an end date cannot precede a start date. A mathematical and calendar rule, exactly
where it belongs.)
When the business retires 'PENDING', we don't touch the orders table. We update the lookup table:
UPDATE order_statuses
SET effective_end_date = SYSTIMESTAMP
WHERE status_code = 'PENDING';
INSERT INTO order_statuses (status_code, description, display_seq, effective_start_date)
VALUES ('AWAITING_PAYMENT', 'Awaiting Payment', 20, SYSTIMESTAMP);
INSERT INTO order_statuses (status_code, description, display_seq, effective_start_date)
VALUES ('AWAITING_STOCK', 'Awaiting Stock', 30, SYSTIMESTAMP);Every historical order referencing 'PENDING' remains fully valid. Meanwhile, any active query or UI dropdown filters on
effective_end_date IS NULL (or evaluates whether SYSTIMESTAMP falls between the start and end dates). New orders cannot select '
PENDING'. Old orders cannot be corrupted.
Better yet, temporal versioning gives you future-dating for free. When operations announces that a new status takes effect on January 1, you insert the row
today with an effective_start_date of January 1. At midnight, it activates automatically. No midnight deployments, no emergency patches, and zero table
locks.
The Metadata Leak
A CHECK constraint treats an enum as a naked, isolated literal. But business vocabulary never stays naked.
Before long, the rest of the team needs context:
- The user interface needs human-readable labels and a coherent sort order rather than raw database codes.
- Reporting pipelines need to know which statuses represent terminal states versus active work in progress.
- Audit systems need to know what the valid vocabulary looked like on a specific date two years ago.
In a reference table, those attributes have a natural home (display_seq, effective_start_date, description). The database
acts as a shared, queryable source of truth.
In a CHECK constraint, the database cannot hold that context. So the context leaks. It leaks into frontend TypeScript switch statements, backend
YAML files, and hardcoded reporting filters. You didn't avoid complexity; you just forced your schema to live in five different application files instead of the
database.
The Portable Rule
This brings us to a reliable boundary for schema design:
- Use CHECK constraints for mathematical, temporal, and physical invariants.
quantity > 0.percentage BETWEEN 0 AND 100.end_date >= start_date. Cross-column requirements like requiring a card token when payment type is credit card. These are arithmetic and physical boundaries. They do not drift because of a company rebrand. - Use Lookup Tables for business vocabulary and lifecycle states. Order statuses, workflow stages, customer tiers, reason codes. Anything that product owners or executives might refine next quarter.
If changing the rule violates the laws of mathematics or calendar time, it belongs in DDL. If changing the rule requires a conversation with product management, it belongs in a table.
Scoped Optimization and AI
In my reply to Tom on that earlier post, I noted that keeping enums out of CHECK constraints is one of the fundamentals that requires an explicit
waiver in my coding assistant instruction files.
It is worth asking why AI assistants reach for CHECK (status IN (...)) almost every single time they are asked to scaffold a schema.
The assistant is not being lazy. It is behaving exactly like an application developer focused on a single user story: it is correctly optimizing for a scope that does not include the third fiscal year.
Within the boundaries of a single prompt, a self-contained CHECK constraint satisfies every immediate functional requirement. It compiles cleanly
in thirty lines of generated SQL, avoids creating auxiliary tables, and avoids the cognitive overhead of foreign keys. The model's horizon is the current
response window. It has no reason to care what happens when marketing changes 'ASAP' to 'SOON' thirty-six months from now.
If we don't guide our tools with clear fundamentals, they will produce code that is locally optimal and globally fragile.
Take the time to build the reference table, give it proper temporal boundaries, and establish the foreign key. It is the only way to ensure that when your data models meet the third fiscal year, the database is still the place telling the truth.
The arguments are mine. Drafted with Gemini 3.8 Flash.
