beginnerintermediateadvanced

PostgreSQL · Constraints and Data Integrity

Constraints and Data Integrity

The five core constraints, composite uniqueness, deferring a check to COMMIT, ON DELETE/ON UPDATE, and why invariants belong in the schema, not just application code

5 constraint typesPRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK
Composite = the combinationeach column can repeat, the pair cannot
ON DELETE CASCADEpropagates — NO ACTION (default) blocks instead
Enforced from any code pathapp validation only protects one path

1.PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK Constraints

PRIMARY KEY uniquely identifies a row and forbids NULL. FOREIGN KEY requires a value to reference an existing row elsewhere. UNIQUE forbids duplicate values (NULLs excepted). NOT NULL forbids a missing value. CHECK enforces an arbitrary boolean condition on a row. Each rejects a bad write outright rather than allowing it.

Key features

PRIMARY KEY = UNIQUE + NOT NULL, plus marking the column as the table's canonical identifier — a table can have only one
FOREIGN KEY requires the referenced column to be UNIQUE or PRIMARY KEY on the other table, and rejects a value with no matching row
UNIQUE allows multiple NULLs by default — NULL is never considered equal to another NULL, so two NULLs do not violate uniqueness
CHECK enforces any boolean expression evaluated per row, e.g. CHECK (price >= 0)
Every constraint is enforced at write time (INSERT/UPDATE) — a violating row is rejected, the transaction sees an error, nothing bad is ever stored

What each constraint actually rejects

What each constraint actually rejects
Constraint	Rejects a write that would...
PRIMARY KEY	duplicate an existing key value, or leave it NULL
FOREIGN KEY	reference a row that does not exist in the other table
UNIQUE	duplicate an existing non-NULL value in that column
NOT NULL	leave the column empty
CHECK (expr)	make expr evaluate to false for that row

Five constraints, five different invariants

PRIMARY KEY

unique + not null identifier

FOREIGN KEY

must reference a real row

UNIQUE

no duplicate non-NULL values

NOT NULL

must be present

CHECK (expr)

arbitrary boolean rule

Command

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    order_number TEXT UNIQUE NOT NULL,
    total NUMERIC NOT NULL CHECK (total >= 0)
);

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK each enforce a different invariant, and all are checked on every write from any code path — application-level validation only protects the one path it was written for.

2.Composite Keys and Composite Unique Constraints

A composite key or composite unique constraint spans more than one column — the uniqueness rule applies to the combination, not to each column individually. Two rows can share a value in one of the columns as long as the combination of all the columns together is still unique.

Key features

PRIMARY KEY (col_a, col_b) makes the combination of col_a and col_b unique and not null — neither column alone needs to be unique
UNIQUE (col_a, col_b) is the non-primary-key equivalent — enforces the combination is unique, allows NULLs per the usual UNIQUE rule
A junction table's natural composite primary key is exactly its two foreign key columns together
Column order in a composite key does not change what is enforced, but does affect which queries can use it as an index efficiently
A composite UNIQUE constraint with any NULL column follows the same NULL-are-never-equal rule as single-column UNIQUE, per column combination

Single-column UNIQUE vs composite UNIQUE

Single-column UNIQUE vs composite UNIQUE
Constraint	What must be unique
UNIQUE (email)	each email value alone
UNIQUE (student_id, course_id)	the combination — student_id and course_id can each repeat separately

Command

sql

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (student_id, course_id)
);

A composite constraint enforces uniqueness on the combination of columns, not each column individually — the standard shape for a junction table's primary key, where each foreign key legitimately repeats across different rows.

3.DEFERRABLE Constraints and Deferred Validation

By default, PostgreSQL checks a constraint immediately after each statement. A DEFERRABLE constraint can instead be checked at the end of the transaction (COMMIT), which allows a transaction to pass through a temporarily "invalid" intermediate state as long as everything is consistent by the time it commits.

Key features

Only PRIMARY KEY, UNIQUE, FOREIGN KEY, EXCLUDE and CHECK constraints (not NOT NULL) can be declared DEFERRABLE in PostgreSQL
DEFERRABLE alone still checks immediately by default (INITIALLY IMMEDIATE); INITIALLY DEFERRED checks at COMMIT instead
SET CONSTRAINTS ALL DEFERRED can defer an already-DEFERRABLE constraint for just the current transaction, without changing its declared default
A non-DEFERRABLE constraint cannot be deferred under any circumstance — the option must be declared on the constraint itself
Deferred checking is most useful for cyclic foreign key references, or an atomic swap of unique values

Constraint timing options

Constraint timing options
Declaration	When checked
NOT DEFERRABLE (default)	immediately after each statement
DEFERRABLE INITIALLY IMMEDIATE	immediately by default, but can be deferred per-transaction with SET CONSTRAINTS
DEFERRABLE INITIALLY DEFERRED	at COMMIT, unless changed per-transaction

Command

sql

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    position INT,
    CONSTRAINT items_position_key UNIQUE (position) DEFERRABLE INITIALLY DEFERRED
);

A DEFERRABLE constraint set to INITIALLY DEFERRED checks at COMMIT instead of after each statement — the standard technique for a multi-step operation (like swapping two unique values) that is only individually valid in a particular order but valid as a whole.

4.ON DELETE and ON UPDATE Behavior

ON DELETE and ON UPDATE tell PostgreSQL what to do to child rows when the parent row they reference is deleted or its key changes. The default, if unspecified, is NO ACTION — reject the delete/update if any child row still references it.

Key features

NO ACTION (the default) and RESTRICT both block the delete/update if a referencing row exists — they differ only in DEFERRABLE timing
CASCADE propagates the delete (or key change) to every referencing child row automatically
SET NULL sets the child's foreign key column to NULL instead of deleting the child row
SET DEFAULT sets the child's foreign key column to its declared DEFAULT value
CASCADE deletes are silent and can ripple through several tables at once — a schema-level decision, not something a query author opts into per statement

The five ON DELETE options

The five ON DELETE options
Option	Effect on child rows when the parent is deleted
NO ACTION (default)	delete rejected if any child still references the parent
RESTRICT	same as NO ACTION for most purposes — rejected immediately
CASCADE	child rows are deleted too, automatically
SET NULL	child's foreign key column becomes NULL
SET DEFAULT	child's foreign key column resets to its DEFAULT value

CASCADE vs SET NULL — two very different answers to "the parent was deleted"

ON DELETE CASCADE

child row is deleted along with the parent
appropriate when the child has no meaning without its parent
e.g. order_items when the order itself is deleted

ON DELETE SET NULL

child row survives, its reference is cleared
appropriate when the child is meaningful independently
e.g. an employee's manager_id, when the manager leaves

Command

sql

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id) ON DELETE RESTRICT
);

ON DELETE/ON UPDATE decide what happens to child rows when a parent is deleted or its key changes — CASCADE propagates the delete, SET NULL severs the link, NO ACTION (the default) blocks it outright. Pick deliberately; the default is not always the intended behavior.

5.Why Critical Invariants Belong in the Database

Application code only enforces a rule for requests that go through that specific code path — a database constraint enforces it for every write, from any code path, forever, including ones that do not exist yet. That is the entire argument for putting critical invariants in the schema rather than relying solely on application-level checks.

Key features

A database constraint is enforced for every write, regardless of which code, script, or tool performs it
Application-level validation only protects the specific code path it is written into
Constraints fail loudly and immediately (a rejected write with a clear error) rather than allowing bad data to be silently stored and discovered later
Not every rule belongs in the database — constraints suit invariants that are always true regardless of business context, not rules with exceptions or that depend on external state
A constraint's failure is also a form of documentation: reading the schema tells a new engineer what must always be true, without reading every application code path

Application validation vs a database constraint

Application validation vs a database constraint
Property	Application-level validation	Database constraint
Protects	only the code path it is written into	every write, from any code path
Discovered by a new team member	by reading application code	by reading the schema directly
Failure mode when bypassed	bad data silently stored	write rejected immediately, loudly

Command

sql

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders ADD CONSTRAINT valid_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Application-level validation only protects the code path it is written into; a database constraint protects every write, from any code path, including ones that do not exist yet — that is why critical invariants belong in the schema.

6.Using Constraints to Prevent Duplicate, Orphaned or Invalid Data

Three common data-quality problems each map to a specific constraint: duplicates are prevented by UNIQUE (or a composite UNIQUE), orphaned rows are prevented by FOREIGN KEY, and invalid values are prevented by CHECK. Recognizing which problem is at hand points directly at which constraint to reach for.

Key features

A duplicate row problem is prevented with UNIQUE, or a composite UNIQUE/PRIMARY KEY for a multi-column uniqueness rule
An orphaned row (referencing something that no longer exists) is prevented with FOREIGN KEY
An invalid value (out of range, wrong format, violates a business rule) is prevented with CHECK
These three constraints are often combined on the same table, each protecting a different aspect of the same row
A constraint added after bad data already exists will fail to apply until the existing violations are cleaned up first

Data-quality problem → constraint that prevents it

Data-quality problem → constraint that prevents it
Problem	Constraint	Example
Duplicate row	UNIQUE	UNIQUE (email) — no two customers share an email
Orphaned row	FOREIGN KEY	order.customer_id must reference a real customer
Invalid value	CHECK	CHECK (age >= 0) — no negative ages

Command

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),   -- prevents orphaned rows
    order_number TEXT UNIQUE NOT NULL,                     -- prevents duplicates
    total NUMERIC NOT NULL CHECK (total >= 0)              -- prevents invalid values
);

Three data-quality problems, three constraints: UNIQUE prevents duplicates, FOREIGN KEY prevents orphaned rows, CHECK prevents invalid values — and each is enforced atomically by the database, avoiding the race conditions an application-side check-then-insert pattern is exposed to.

Spotted a mistake on this page? Tell us.

Read Sheet