Constraints as Guardrails

A constraint is the only rule that survives a second service, an intern's backfill script and a hand-typed UPDATE. Everything else — the validation in the API, the check in the ORM, the convention in the wiki — is a habit that holds right up to the first writer who skips it. Cartwheel has already run that experiment twice.

inventory.on_hand has gone negative on two occasions, both times from a stock-correction script that talks to the database directly. orders.status is a free-text column that currently contains delivered, Delivered and deliverd, and every query that filters on it is wrong for some rows and returns a number anyway. The one invariant that has never been violated is courier double-booking, and the reason is that Chapter 2 turned it into an EXCLUDE constraint on courier_shifts rather than a rule the dispatch service has to remember. This topic gives the rest of Cartwheel's invariants the same standing — on live tables of 40 million rows, without a lock that stops checkout.

The Standard Set, and What Postgres Adds

The standard set runs to six kinds. NOT NULL says the value is present. CHECK evaluates any boolean expression over one row. UNIQUE and PRIMARY KEY are enforced by an index that the constraint owns. FOREIGN KEY ties a value to a row in another table. EXCLUDE generalizes uniqueness to any operator a GiST index can evaluate, which is the mechanism already holding courier_shifts together.

ALTER TABLE inventory
  ADD CONSTRAINT inventory_on_hand_non_negative
      CHECK (on_hand >= 0);

One statement, and no writer anywhere can drive stock below zero again — not the API, not the correction script, not a hand-typed UPDATE. Two details of the standard set are Postgres-specific and worth having straight. Since 18, NOT NULL is stored in pg_constraint like the others, which means it can be named, inherited deliberately, and — as the fifth section uses — created invalid and validated later. And a UNIQUE constraint is not merely backed by an index, it owns one, so the index cannot be dropped independently and ON CONFLICT needs a real unique constraint or index to aim at. inventory gets one in the same migration: the missing primary key that Chapter 1's catalogue query turned up becomes PRIMARY KEY (product_id, warehouse_id), and that constraint is what the warehouse upsert in Chapter 4 arbitrates on.

A Lookup Table for orders.status

Chapter 2 rejected an enum for orders.status on the grounds that the value set belongs to the business: labels can be added and renamed, never removed, and reordering means dropping the type along with every view that depends on it. The alternative is a lookup table with a foreign key. Statuses become rows, so adding one is an INSERT, retiring one is a flag, and the sort order that an enum gave for free becomes a column any query can read.

CREATE TABLE order_statuses (
    status      text PRIMARY KEY,
    label       text NOT NULL,
    sort_order  int  NOT NULL,
    is_terminal boolean NOT NULL DEFAULT false
);

INSERT INTO order_statuses VALUES
  ('pending',   'Pending',   1, false),
  ('picking',   'Picking',   2, false),
  ('dispatched','Dispatched',3, false),
  ('delivered', 'Delivered', 4, true),
  ('cancelled', 'Cancelled', 5, true),
  ('refunded',  'Refunded',  6, true);

ALTER TABLE orders
  ADD CONSTRAINT orders_status_fk
      FOREIGN KEY (status) REFERENCES order_statuses (status)
      ON DELETE RESTRICT NOT VALID;

ON DELETE RESTRICT is the right action on a lookup table: a status that 9 million orders still reference must not be deletable, and RESTRICT refuses immediately rather than letting the check be deferred to commit the way the default NO ACTION would. The constraint goes on NOT VALID because the table already contains Delivered and deliverd, and those rows have to be repaired before anything can be validated. New writes are constrained from the moment that statement commits; the 40 million existing rows are not examined at all.

The Foreign-Key Index Postgres Does Not Create

A foreign key must reference a primary key or a unique constraint, so the referenced side always has an index — that is a precondition, not a courtesy. The referencing side gets nothing. Postgres does not create an index on orders.customer_id when the foreign key is declared, and the reason given is that there are too many reasonable ways to index it for the system to pick one.

DELETE FROM customers WHERE id = 77219;
-- to prove no order references this customer, Postgres must
-- look for matching rows in a 40-million-row table

CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

Without that index, every delete of a parent row costs a sequential scan of orders, and a batch of deletions costs one per row. The four-minute delete that holds locks the whole time is the symptom; nothing in the schema explains it, because the missing object is the one that was never created — Chapter 8 covers how to choose it. The same scan happens on any update of a referenced key, and once for every foreign key pointing at the table.

Adding a Constraint Without a Long Lock

A plain ADD CONSTRAINT verifies every existing row before it commits. On orders that is a full scan of 40 million rows inside the same transaction that holds the lock, and on a foreign key it is a lookup per row. NOT VALID splits the work: the constraint is recorded and enforced against every insert and update from that moment, and the existing rows are simply not examined.

ALTER TABLE orders
  ADD CONSTRAINT orders_total_non_negative
      CHECK (total >= 0) NOT VALID;      -- brief lock, no scan

-- later, at leisure, and repeatable if it fails
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_non_negative;

The second statement scans the table to check the rows that predate the constraint, and it takes only a SHARE UPDATE EXCLUSIVE lock, which blocks neither readers nor writers. The reason that is safe is worth stating: every concurrent transaction is already enforcing the constraint on anything it writes, so validation only has to look at rows that were there before. What NOT VALID changes is duration rather than lock level, and the lock the first statement takes is a different question: ADD CONSTRAINT with a CHECK still takes ACCESS EXCLUSIVE, and ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables, which blocks writers but not readers.

SET NOT NULL Without the Scan

Declaring a column NOT NULL looks trivial and is not: ordinarily Postgres scans the entire table under ACCESS EXCLUSIVE to prove no null is present, and on orders that lock is held for the length of the scan. Since 12 there is a way around it. If a valid CHECK constraint already proves that no null can exist, and it is not dropped in the same command, the scan is skipped. Cartwheel's four missing public_id values have been filled in by now, so the column is ready to be closed properly.

ALTER TABLE orders ADD CONSTRAINT orders_public_id_nn
      CHECK (public_id IS NOT NULL) NOT VALID;   -- instant

ALTER TABLE orders VALIDATE CONSTRAINT orders_public_id_nn;
                                       -- scans, blocks nothing

ALTER TABLE orders ALTER COLUMN public_id SET NOT NULL;
                                       -- milliseconds, no scan

The expensive part — reading 40 million rows — happens in the middle statement, under a lock that lets checkout carry on. Only the third statement takes ACCESS EXCLUSIVE, and it holds it for the time it takes to update one catalogue row. Postgres 18 offers the same shape without the scaffolding: because NOT NULL is now a real constraint in pg_constraint, it can be added NOT VALID directly (ADD CONSTRAINT … NOT NULL public_id NOT VALID), validated under the same weak lock, and the column then becomes properly not-null with no full scan of its own. On 17 and earlier, only the CHECK route exists.

Deferrable Constraints

By default a constraint is checked at the end of the statement that violated it. DEFERRABLE INITIALLY DEFERRED postpones the check to COMMIT, which makes a circular update legal: swapping two values that must stay unique no longer needs a temporary third value nobody wants in the table. Only four kinds can be deferred — UNIQUE, PRIMARY KEY, EXCLUDE and foreign keys. NOT NULL and CHECK are always checked immediately, and no setting changes that.

ALTER TABLE order_statuses
  ADD CONSTRAINT order_statuses_sort_uq UNIQUE (sort_order)
      DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  UPDATE order_statuses SET sort_order = 3 WHERE status = 'delivered';
  UPDATE order_statuses SET sort_order = 4 WHERE status = 'dispatched';
COMMIT;   -- both rows briefly held sort_order = 3, and that was fine

Deferral is not free. The violation now surfaces at COMMIT, far from the statement that caused it, so the error message points at the transaction rather than at the line of code — and a bulk load that relies on deferral holds the pending checks until the end, which is memory the server has to find. The sharper cost is that a deferrable unique constraint cannot serve as the arbiter of an ON CONFLICT clause; only non-deferrable constraints and unique indexes are supported there.