Constraints Are Your Application's Spine

Every application layer that validates data eventually has a bug, a race, or a migration script that bypasses it. The database is the only place invariants can't be skipped — encode them there and the app can be wrong without corrupting anything.

Check constraints encode business rules

ALTER TABLE bookings ADD CONSTRAINT sensible_range
  CHECK (check_out > check_in);

ALTER TABLE orders ADD CONSTRAINT positive_total
  CHECK (total_cents >= 0);

total_cents < 0 becomes impossible — not "probably handled in the service layer," impossible. Retroactively adding one to dirty data fails loudly, which is the point: NOT VALID lets you add the constraint to a huge table without scanning it (ALTER TABLE ... ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT in a second, lock-light pass).

Foreign keys are referential integrity

ON DELETE CASCADE removes dependent rows; ON DELETE SET NULL orphans the reference politely; default RESTRICT refuses the delete — choose per relationship, and always index the referencing column or cascades become table scans.

Exclusion constraints for overlap rules

"No two bookings overlap the same room" can't be written as a unique constraint:

ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, slot WITH &&);

This rejects double-bookings at the storage layer — with a lockless GiST index, no transaction ordering required. The class of bug (race during check-then-insert) simply ceases to exist.

Generated columns denormalize safely

ALTER TABLE products ADD COLUMN search_text tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || coalesce(description,''))) STORED;

A maintained-in-database derived column — always consistent, indexable, and no application code can forget to update it.

Field notes

• UNIQUE is an index plus a constraint — the constraint is what protects you; use it.
• Adding NOT NULL to an existing column needs a CHECK ... NOT VALID + backfill + VALIDATE + SET NOT NULL on big tables to avoid a full-table scan under lock.