When the Guardrail Stops Guarding

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.