Constraints, keys, and Postgres data types

A team stores money as REAL. After a year of accumulated transactions they find cents-level discrepancies they cannot explain. The type was the bug — float arithmetic loses precision. The fix is a schema change that touches every row.

Codd’s relational model vs SQL

Edgar Codd’s 1970 paper formalised relations as sets of tuples drawn from typed domains, and defined a closed algebra of operations (selection, projection, join, union, intersection, difference) — closed because each operation takes relations as input and produces a relation as output. SQL is a non-strict implementation of that algebra; it adds NULL (which Codd disliked), ORDER (rows are conceptually unordered), and duplicate rows (relations have no duplicates). Knowing the gap explains the rough edges:

• NULL = NULL is NULL, not true — three-valued logic.
• ORDER BY is required to guarantee row order — the engine may return any order without it.
• DISTINCT exists because the engine must keep duplicates unless you ask.

Treat the gap as “SQL = relational algebra plus practical compromises” and the surprises stop being surprising.

The five constraint kinds

Surrogate vs natural keys

A natural key is data that already exists in the business domain (user email, product SKU, order number). A surrogate key is database-generated, opaque, and meaningful only inside the database (BIGSERIAL, UUID).

Production default in 2026: surrogate key as the primary key, plus a UNIQUE NOT NULL constraint on the business-natural key. Why: natural keys change (a customer changes their email), and a primary key change cascades through every foreign key referencing it — operationally expensive and often impossible at scale. Surrogate keys never change.

The exception: pure join tables (favourites: user_id, item_id) often use the composite of foreign keys as the PK — the relationship itself is the identity, no surrogate needed.

UUID vs BIGSERIAL. UUIDs are globally unique (good for distributed inserts, multi-region, offline-first clients) but bigger (16 bytes vs 8) and worse for index locality (random UUIDv4 fragments B-trees). UUIDv7 (time-ordered, RFC 9562) fixes the locality issue and is the modern default where UUID is wanted. BIGSERIAL is smaller, sequential, and cache-friendly — pick it when globally-unique IDs are not needed.

Postgres data types: pick the narrowest that fits

Postgres has the richest type system of any mainstream database. The type is the first line of constraint — a correctly-typed column catches 80% of bad data before any CHECK constraint runs.