Constraints in Depth
Constraints are rules enforced by PostgreSQL itself, at the row level, on every INSERT, UPDATE, and in some cases DELETE, meaning invalid data simply cannot enter the table regardless of which application, script, or ad-hoc psql session is writing to it -- this is fundamentally different from validating data only in application code, which can be bypassed by any client that talks to the database directly. Relying on database-level constraints as the last line of defense means your data stays correct even as application code evolves, gets rewritten in a new language, or is temporarily buggy during a deploy.
Cricket analogy: A database constraint is like the third umpire's automated no-ball detection camera -- it catches an overstepped delivery regardless of whether the on-field umpire (application code) happened to notice, providing a backstop that never gets tired or distracted.
PRIMARY KEY, UNIQUE, and NOT NULL
A PRIMARY KEY constraint is really shorthand for UNIQUE plus NOT NULL, automatically backed by a unique B-tree index, and a table can have only one; a UNIQUE constraint enforces distinctness on its own without implying NOT NULL, and PostgreSQL treats multiple NULLs as distinct from each other by default so a UNIQUE column can hold several NULL rows unless you add a NULLS NOT DISTINCT modifier (added in PostgreSQL 15). NOT NULL is the simplest and cheapest constraint to check, and it's worth defaulting to NOT NULL on any column where a missing value would represent a data-quality bug rather than a legitimate business state.
Cricket analogy: PRIMARY KEY is like a player's unique jersey number on a team roster -- no two players can share it and every player must have one, whereas a UNIQUE 'nickname' column could allow several players with no nickname at all.
FOREIGN KEY and Referential Actions
A FOREIGN KEY constraint ensures a column's value must match an existing value in a referenced table's PRIMARY KEY or UNIQUE column, and you must decide what happens on deletion or update of the parent row via ON DELETE/ON UPDATE clauses: CASCADE propagates the change, RESTRICT or NO ACTION blocks it if dependents exist, SET NULL clears the reference, and SET DEFAULT resets it to a default value. Choosing the wrong referential action is a common source of production incidents -- ON DELETE CASCADE on a users table, for instance, can silently wipe out orders, invoices, and audit logs when someone deletes a test account, so it deserves deliberate thought per relationship rather than a copy-pasted default.
Cricket analogy: ON DELETE CASCADE is like disbanding a franchise (parent) and automatically releasing every contracted player (child rows) from their deals -- powerful, but you'd never want that to fire accidentally on a minor roster edit.
-- CHECK, UNIQUE, FOREIGN KEY, and EXCLUDE constraints together
CREATE TABLE reservations (
id serial PRIMARY KEY,
room_id integer NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,
guest_email text NOT NULL,
nights integer NOT NULL CHECK (nights > 0),
during tsrange NOT NULL,
CONSTRAINT uq_room_email UNIQUE (room_id, guest_email),
-- Prevent overlapping bookings for the same room using GiST + EXCLUDE
EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);A CHECK constraint referencing only the row being inserted cannot enforce cross-row business rules like 'total bookings per room per day must not exceed capacity' -- for overlapping-range or aggregate rules, use an EXCLUDE constraint (as shown above) or a trigger, since CHECK alone only sees one row at a time.
Adding a NOT NULL constraint to an existing large table can require a full table scan to validate. In PostgreSQL 12+, you can add a NOT VALID CHECK constraint mimicking NOT NULL and validate it later with VALIDATE CONSTRAINT to avoid a long exclusive lock during peak hours.
- Constraints are enforced by the database itself, protecting data integrity regardless of which client writes to it.
- PRIMARY KEY combines UNIQUE and NOT NULL and is backed by a unique index; only one is allowed per table.
- UNIQUE columns can hold multiple NULLs by default unless NULLS NOT DISTINCT is specified (PostgreSQL 15+).
- FOREIGN KEY referential actions (CASCADE, RESTRICT, SET NULL, SET DEFAULT) must be chosen deliberately per relationship.
- CHECK constraints validate a single row's data and cannot enforce cross-row aggregate rules.
- EXCLUDE constraints (often with GiST indexes) enforce rules like 'no overlapping ranges', which CHECK cannot express.
- Adding constraints to large existing tables can be done more safely using NOT VALID plus a later VALIDATE CONSTRAINT.
Practice what you learned
1. What two constraints does a PRIMARY KEY effectively combine?
2. By default, how many NULL values can a UNIQUE column hold?
3. Why can ON DELETE CASCADE be risky on a foreign key from orders to users?
4. What kind of rule can a CHECK constraint NOT enforce?
5. What is the benefit of adding a constraint as NOT VALID before running VALIDATE CONSTRAINT?
Was this page helpful?
You May Also Like
Data Types in Depth
A practical deep dive into PostgreSQL's rich type system, covering numeric precision, text vs. varchar, JSONB, arrays, and enum/domain types.
PostgreSQL Architecture Overview
A tour of how PostgreSQL is built internally: the process model, shared memory, the write-ahead log, and the MVCC storage engine that together deliver durability and concurrency.
Views and Materialized Views
How PostgreSQL views provide reusable query abstractions and how materialized views trade freshness for read performance by persisting query results to disk.