Declarative constraints in PostgreSQL: CHECK, UNIQUE and foreign keys with ON DELETE

What it is

The PostgreSQL constraints documentation describes CHECK (a Boolean expression evaluated per row, satisfied when it is true or null), NOT NULL, UNIQUE (which treats NULLs as distinct unless NULLS NOT DISTINCT is written), PRIMARY KEY, EXCLUDE and FOREIGN KEY. A foreign key's referential action says what happens when the referenced row is deleted: the default NO ACTION raises an error if referencing rows still exist when the constraint is checked, which may be deferred to later in the transaction; RESTRICT prevents the deletion and cannot be deferred; CASCADE deletes the referencing rows; SET NULL and SET DEFAULT rewrite the referencing columns. The documentation states that declaring a foreign key does not automatically create an index on the referencing columns.

Why it matters

Constraints are the only validation that applies to every writer: the application, a psql session, a migration, a bulk import. A CHECK that a quantity is positive or a UNIQUE on an email address removes a class of bugs from every code path at once, and the error arrives at the statement that caused it rather than in a report weeks later.

How to apply

• Make every column NOT NULL unless "unknown" is a meaning the application handles; add CHECK constraints for rules that never change (price >= 0, starts_at < ends_at).
• Decide ON DELETE per relation: CASCADE for owned children (order items of an order), RESTRICT or NO ACTION for references a person must resolve (a product still referenced by orders), SET NULL for optional links.
• Index the referencing column of every foreign key that is used in joins or whose parent rows get deleted; without it, a parent deletion scans the child table.
• Name constraints (CONSTRAINT orders_total_nonneg CHECK (...)) so that error messages and migrations are readable.
• Add foreign-key, CHECK and not-null constraints to large tables as NOT VALID (the ALTER TABLE documentation allows the option only for these kinds) and run VALIDATE CONSTRAINT afterwards, so existing rows are checked without holding the strongest lock for the whole scan.
• Use DEFERRABLE INITIALLY DEFERRED only for constraints that must be violated temporarily inside a transaction, such as cyclic references.

Pitfalls

A CHECK cannot look at other rows or tables; use EXCLUDE constraints or triggers for that. CASCADE on the wrong relation deletes far more than intended and does so silently. A UNIQUE constraint on a nullable column allows several NULLs unless NULLS NOT DISTINCT is specified. Constraints checked in application code only are bypassed by every other client.