Constraints: The Rules the Database Enforces

In one line

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT make invalid data impossible regardless of which service writes it. FK actions (RESTRICT, CASCADE, SET NULL) define what happens on parent changes, deferrable constraints are checked at commit, and PostgreSQL exclusion constraints prevent overlapping ranges such as double bookings.

0/3 · 0%

Think of it like this

A form with required fields and validation. The application can check, but the database is the final clerk who refuses to file an incomplete form no matter who hands it in: an old service, a script, or a support engineer in psql.

Key ideas

01
NOT NULL is the most valuable constraint: it removes a whole class of three-valued-logic bugs (Phase 4.6). Make columns NOT NULL unless absence is a real state.
02
UNIQUE enforces business identity (one account per email, one review per user per product). In PostgreSQL, NULLs are distinct by default, so UNIQUE(email) allows many NULLs; PostgreSQL 15+ supports UNIQUE NULLS NOT DISTINCT.
03
CHECK expresses row-level rules: CHECK (quantity > 0), CHECK (end_at > start_at), CHECK (status IN (...)). It cannot look at other rows or tables; use triggers or application logic, and preferably unique or exclusion constraints, for cross-row rules.
04
FK actions: ON DELETE RESTRICT/NO ACTION (default; block deleting a referenced parent), CASCADE (delete children, good for weak entities like order lines), SET NULL (optional relationships). Never CASCADE from important history (deleting a customer must not silently delete invoices).
05
Deferrable constraints (DEFERRABLE INITIALLY DEFERRED) are checked at COMMIT instead of per statement, needed for swapping unique positions or inserting circular references in one transaction.
06
Exclusion constraints generalise UNIQUE to operators: EXCLUDE USING gist (room_id WITH =, stay WITH &&) forbids two bookings of the same room with overlapping date ranges, enforced atomically even under concurrency.

Code & diagrams

constraints.sqlsql

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id   bigint NOT NULL REFERENCES room_unit(id) ON DELETE RESTRICT,
  guest_id  bigint NOT NULL REFERENCES guest(id),
  stay      daterange NOT NULL CHECK (NOT isempty(stay)),
  status    text NOT NULL DEFAULT 'confirmed'
            CHECK (status IN ('confirmed','cancelled')),
  EXCLUDE USING gist (room_id WITH =, stay WITH &&) WHERE (status = 'confirmed')
);

INSERT INTO booking (room_id, guest_id, stay) VALUES (7, 1, '[2026-10-01,2026-10-04)');
INSERT INTO booking (room_id, guest_id, stay) VALUES (7, 2, '[2026-10-03,2026-10-05)');
-- ERROR:  conflicting key value violates exclusion constraint "booking_room_id_stay_excl"

-- deferrable unique: swap two positions in one transaction
CREATE TABLE playlist_item (
  playlist_id bigint, position int, track_id bigint,
  UNIQUE (playlist_id, position) DEFERRABLE INITIALLY DEFERRED
);

add-constraint-safely.sqlsql

On a big table, add constraints without a long exclusive lock.

ALTER TABLE orders ADD CONSTRAINT orders_amount_positive
  CHECK (amount > 0) NOT VALID;               -- instant: only new rows checked
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;  -- scans without blocking writes

Interview problem

The problem

Prevent double-booking of meeting rooms

Two people can click "book" for the same room and overlapping time at the same moment. The application checks availability first, but double bookings still happen. Fix it in the database.

When it breaks

ON DELETE CASCADE from customer to orders and invoices

What you see

A support tool deletes a test-looking customer and years of financial records disappear, silently and legally problematically.

Fix & prevent

Use RESTRICT for history, soft-delete or anonymise customers (Phase 14), and reserve CASCADE for true weak entities.

Adding a CHECK or FK to a 500M-row table in one statement

What you see

The ALTER holds a lock and scans the whole table; writes queue behind it and the application times out.

Fix & prevent

ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT separately; set lock_timeout on migrations (Phase 13).

Explain it without notes

Why can't a CHECK constraint prevent overlapping bookings?

When do you need a deferrable constraint?

Practice

Enforce "at most one active subscription per user" while allowing many cancelled ones.

Trade-offs

↔
Constraints cost a little write overhead and make migrations more careful; in return, data stays valid for every writer, forever.

Done when you can

I can express business rules as constraints, including partial unique and exclusion constraints, and add them safely.

Command Palette