Constraints Are Contracts: NOT NULL, CHECK, UNIQUE, FK

Every incident review has a line that reads "we assumed X could never happen" — a null email, a negative quantity, a duplicate job run. Application validation helps, but apps accumulate writers constantly: migrations, admin scripts, background jobs, the second service nobody remembered. The database is the one chokepoint every write passes through, so it is the only place an invariant can actually be guaranteed. Postgres constraints are cheap, enforced atomically with each write, and they turn "should never happen" into "cannot happen."

A table built to survive looks like this:

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   bigint NOT NULL REFERENCES tenants(id),
    email       text NOT NULL,
    status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','paid','shipped','refunded')),
    quantity    integer NOT NULL CHECK (quantity > 0),
    total_cents numeric(12,2) NOT NULL CHECK (total_cents >= 0),
    created_at  timestamptz NOT NULL DEFAULT now()
);

NOT NULL is the highest-value constraint in the list — every nullable column is a special case every reader has to handle forever. CHECK is nearly free and runs on every insert and update. UNIQUE is not just hygiene: it is what makes INSERT ... ON CONFLICT work, which is how you do race-safe upserts later. Foreign keys document the shape of your data and prevent orphans, but decide ON DELETE behavior deliberately — CASCADE is convenient and quietly destructive; RESTRICT is the safer default for anything money-adjacent.

Adding constraints to big existing tables without a long lock is its own skill — NOT VALID, backfill, then VALIDATE CONSTRAINT. That is the migration lesson; for now, know the escape hatch exists.

Try this: create the table above in a scratch database and attack it from psql: a null email, a negative quantity, an invented status, a second insert colliding on a unique key. Note the SQLSTATE codes Postgres returns — 23505 for unique violations, 23502 for not-null, 23514 for check. Your app code can catch these precisely instead of string-matching errors.

Pitfalls

• varchar(50) is not a real constraint in Postgres — text stores identically; if you need a length rule, write CHECK (char_length(email) <= 254).
• A CHECK only fires on new writes. Dirty rows already in the table stay dirty — audit first, constrain second.
• An FK means every child write takes a key-share lock on the parent row. Fine almost always; it matters when thousands of children point at one hot parent row.