🧷Constraints overview
Declarative invariants the engine enforces
Constraints shrink the set of tuples your tables may hold: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT (plus generated-column rules on some engines). They survive ORM refactors, race windows between microservices, and ad hoc SQL from analysts, treat them as the contract your application code must satisfy, not optional documentation.
📋Constraint cheat sheet
What each one guarantees at insert/update (and sometimes delete) time.
| Constraint | What it guarantees |
|---|---|
| NOT NULL | The column may not contain unknown (NULL) values, domain is mandatory. |
| UNIQUE | No two rows share the same non-NULL key tuple (NULLs usually allowed multiple times). |
| PRIMARY KEY | UNIQUE + NOT NULL, one preferred row identifier per table; referenced by FKs. |
| FOREIGN KEY | Child values exist in the parent key (or are NULL if the column is nullable): referential integrity. |
| CHECK | Row-level predicate must be TRUE or UNKNOWN, rejects impossible combinations. |
| DEFAULT | Substitute value when INSERT omits the column, does not retroactively fix old NULLs alone. |
Inspect constraints (PostgreSQL catalog)
SELECT conname, contype, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'core.visits'::regclass;| conname | contype | def |
|---|---|---|
| visits_pkey | p | PRIMARY KEY (visit_id) |
| fk_visits_patient | f | FOREIGN KEY (patient_id) REFERENCES core.patients(patient_id) |
Integrity only in the application layer
Two services racing, a batch job, or a support console can insert rows your UI never would, bugs become forensic data archaeology.
Duplicate defense in DB + tests for impossible states
Encode cross-row rules with FKs and UNIQUE; encode scalar rules with NOT NULL and CHECK; keep app validation for UX latency, not sole correctness.
Deferred constraints
Some engines allow DEFERRABLE INITIALLY DEFERRED foreign keys, checks run at COMMIT, useful for ordered bulk loads. Document who is allowed to use them; misuse hides violations until commit.
Indexing + FK
Parent PKs are indexed automatically; child FK columns need explicit indexes for delete performance and join probes, missing child indexes is a top OLTP regression.
Migration discipline
Every constraint change should ship with a backfill plan, a rollback DDL snippet, and a query that proves zero violating rows before the VALIDATE / NOT NULL phase on large tables.
TL;DR
Constraints declare invariants the engine enforces: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. They turn data rules from “team memory” into errors at commit time, cheap for correctness, sometimes expensive to add on huge legacy tables (validate + index build). Prefer named constraints so failures in logs and ORMs identify the exact rule.