Constraints: integrity the engine enforces

The point: integrity belongs in the engine

Every rule about valid data — “price is never negative”, “an order points at a real user”, “no two rooms double-booked” — can live in two places: scattered through application code, or declared once on the table. Application checks are advisory: they run only on the paths that remembered to call them, and they lose every concurrency race. A constraint is enforced: it runs inside the same transaction as the write, holds the right locks, and rejects bad data atomically no matter which service, script, or psql session attempts it. (The databases track’s relational-model chapter covers constraints and keys from the theory side; here we wire them up in Postgres.)

NOT NULL, CHECK, UNIQUE, PRIMARY KEY

The everyday three. NOT NULL forbids a missing value. CHECK enforces a boolean predicate per row — the workhorse for business rules. UNIQUE forbids duplicate values and is backed by a unique index, so it costs an index per constraint. PRIMARY KEY is exactly NOT NULL + UNIQUE, plus the marker that this is the identity of the row (one per table).

CREATE TABLE products (
  id          bigint PRIMARY KEY,
  sku         text NOT NULL UNIQUE,                 -- no two products share a SKU
  price_cents bigint NOT NULL CHECK (price_cents >= 0),
  status      text NOT NULL CHECK (status IN ('draft','active','archived'))
);

A CHECK can reference multiple columns of the same row (CHECK (sale_price <= price_cents)) but not other rows or other tables — it’s strictly per-row. For “valid across rows” you need UNIQUE, EXCLUDE, or a trigger.

Foreign keys and ON DELETE / ON UPDATE actions

A FOREIGN KEY says “this column must match an existing row in another table” — referential integrity. The interesting part is what happens when the referenced row is deleted or its key changes. You declare the action:

• ON DELETE RESTRICT (and the similar NO ACTION) — refuse to delete the parent while children exist. The safe default for most relationships; it surfaces accidental cascading deletes as errors.
• ON DELETE CASCADE — delete the children too. Right for true ownership (delete an order, its line items go with it); dangerous when the “child” is shared or valuable.
• ON DELETE SET NULL — keep the child but null its reference. For optional links (an employee’s manager_id when the manager leaves).

CREATE TABLE orders (
  id      bigint PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
  id       bigint PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,  -- owned by the order
  product_id bigint NOT NULL REFERENCES products(id) ON DELETE RESTRICT
);

Note the deliberate mix: deleting an order cascades to its order_items (they have no meaning without it), but you can’t delete a product that any order item references — that would rewrite history. Choosing the action per relationship is the modelling.

DEFERRABLE: check at COMMIT, not at statement

By default a constraint is checked immediately, the moment a statement runs. That breaks cyclic or order-dependent writes — inserting two rows that reference each other, or reordering a list where positions must stay unique mid-update. A DEFERRABLE INITIALLY DEFERRED constraint is checked once, at COMMIT, so the data can be temporarily inconsistent within the transaction as long as it’s consistent when you commit.

ALTER TABLE order_items
  ADD CONSTRAINT uniq_position UNIQUE (order_id, position)
  DEFERRABLE INITIALLY DEFERRED;
-- now you can swap positions 1 and 2 in two UPDATEs;
-- the unique check runs only at COMMIT, when they're consistent again.

EXCLUDE: UNIQUE generalized to any operator

UNIQUE says “no two rows are equal on these columns.” EXCLUDE generalizes that to any operator: “no two rows conflict under operator X.” The killer use is the no-overlap booking — combine a range type with the overlap operator && and a GiST index, and the database makes double-booking physically impossible:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  id      bigint PRIMARY KEY,
  room_id bigint NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)  -- same room AND overlapping time → reject
);

This is the atomic fix for the Hook’s race. Two concurrent inserts for the same room and overlapping window can’t both succeed: the GiST index serializes the conflict check, and the second insert is rejected. No read-then-write window, no application logic, no race.