beginnerintermediateadvanced

PostgreSQL · Keys and Relationships

Primary Keys, Foreign Keys and Relationship Design

Referential integrity as an enforced guarantee, where the foreign key goes per relationship shape, junction tables as real entities, composite foreign keys, and auditing a CASCADE chain before it deletes too much

FK = enforced, not conventiona plain int column proves nothing
1:1 needs UNIQUE toowithout it, silently degrades to 1:many
Attributed junction tablesgrade/role/quantity make it a real entity
CASCADE blast radiusinvisible from one table — trace pg_constraint

1.Understanding Referential Integrity

Referential integrity is the guarantee that a foreign key value always points to a row that genuinely exists. PostgreSQL enforces it automatically for any column declared REFERENCES another table — an INSERT or UPDATE that would point at a nonexistent row is rejected outright.

Key features

A FOREIGN KEY constraint is what makes referential integrity enforced rather than merely assumed
The referenced column must be UNIQUE or PRIMARY KEY on the other table — integrity requires the target to be unambiguous
Referential integrity is checked on both sides: inserting/updating a child row, and deleting/updating a referenced parent row
A table with no foreign key constraint can still store an integer column that "means" a reference — but nothing stops it from becoming stale
Referential integrity failures show up as either a rejected write (if enforced) or a silently orphaned row (if not)

With and without an enforced foreign key

With and without an enforced foreign key
Property	plain INT column	INT REFERENCES parent(id)
Can reference a nonexistent row?	yes, silently	no — rejected at write time
Deleting the referenced row	orphans the child silently	blocked, cascaded, or nulled — per ON DELETE
Who enforces correctness?	application code, if it remembers to	PostgreSQL itself, on every write

A plain integer column vs an enforced foreign key

customer_id INT (no constraint)

nothing verifies the value refers to a real customer
a deleted customer silently orphans every order that "referenced" them
correctness depends entirely on application code remembering to check

customer_id INT REFERENCES customers(id)

every INSERT/UPDATE is checked against customers
deleting a referenced customer is blocked (or cascaded/nulled, per ON DELETE)
correctness is guaranteed by PostgreSQL itself

Command

sql

CREATE TABLE customers (id SERIAL PRIMARY KEY);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id));

Referential integrity is the guarantee that a foreign key value always points to a row that genuinely exists — a plain integer column only represents a relationship by convention; a real FOREIGN KEY constraint enforces it on every write.

2.Modeling One-to-One, One-to-Many and Many-to-Many

One-to-one: a foreign key with an added UNIQUE constraint, on either table. One-to-many: a plain foreign key on the "many" side. Many-to-many: neither table can hold it alone — it needs a junction table with a foreign key to each side.

Key features

One-to-one: FOREIGN KEY + UNIQUE together on one side — UNIQUE is what prevents it from becoming one-to-many
One-to-many: a plain FOREIGN KEY on the "many" side, with no UNIQUE constraint needed on that column
Many-to-many: a junction table with two FOREIGN KEY columns, typically forming a composite PRIMARY KEY together
The choice of which table holds a one-to-one foreign key is often about which side is more likely to be optional
A one-to-one relationship without the UNIQUE constraint silently degrades into an unintentional one-to-many

Where the foreign key lives, per relationship shape

Where the foreign key lives, per relationship shape
Shape	Foreign key location	Extra constraint needed
One-to-one	either table (pick one)	UNIQUE on the foreign key column
One-to-many	the "many" side	none — plain foreign key is enough
Many-to-many	a new junction table	composite PRIMARY KEY on both foreign keys together

Command

sql

-- one-to-one
CREATE TABLE user_profiles (user_id INT PRIMARY KEY REFERENCES users(id), bio TEXT);

-- one-to-many
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id));

-- many-to-many
CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

One-to-many needs only a plain foreign key on the "many" side. One-to-one needs that same foreign key PLUS a UNIQUE constraint — without it, a "one-to-one" relationship is actually one-to-many. Many-to-many always needs a junction table, since neither original table can hold a multi-valued reference in one column.

3.Using Junction Tables for Many-to-Many Relationships

A junction table's minimum shape is two foreign keys, one to each related table, usually forming a composite primary key together. Once the relationship itself has its own attributes — an enrollment date, a role, a quantity — the junction table becomes a real entity in its own right, sometimes warranting its own surrogate key.

Key features

A pure association junction table needs only its two foreign keys, usually as a composite PRIMARY KEY
A junction table carrying attributes about the relationship (enrolled_at, role, quantity) is a real entity, not just plumbing
A junction table needs its own surrogate key only if some other table needs to reference a specific relationship row individually
ON DELETE CASCADE on both foreign keys is common for a junction table — deleting either side's parent usually should remove the association row too
A junction table can itself be the "one" side of another one-to-many relationship, once it has its own identity

Pure association vs a junction table with its own attributes

Pure association vs a junction table with its own attributes
Shape	Primary key	When it applies
Pure association	composite (student_id, course_id)	the relationship itself carries no extra data
Attributed relationship	composite, or its own surrogate id	the relationship has data like enrolled_at, grade, role

Command

sql

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id INT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
    grade TEXT,
    UNIQUE (student_id, course_id)
);

A pure association junction table needs only its two foreign keys as a composite primary key; once the relationship itself carries data (a grade, a role, a quantity), the junction table is a real entity and may warrant its own surrogate key, with UNIQUE preserving the original pairing constraint.

4.Understanding Composite Foreign Keys

A composite foreign key references a composite primary or unique key on another table — the referencing table must supply matching values for every column in the combination, not just one. It is needed whenever the table being referenced can only be uniquely identified by more than one column together.

Key features

FOREIGN KEY (col_a, col_b) REFERENCES parent(col_a, col_b) requires the referenced pair to be UNIQUE or PRIMARY KEY on the parent
All columns in a composite foreign key must be supplied together — partially matching a composite key is not possible
A composite foreign key is common when referencing a junction table's composite primary key directly
Column order in the composite foreign key must correspond to the column order in the referenced composite key
A composite foreign key can itself be part of NULL — if all its columns are NULL, PostgreSQL considers the constraint satisfied (MATCH SIMPLE, the default)

Single-column vs composite foreign key

Single-column vs composite foreign key
Referenced table's key	Foreign key shape
PRIMARY KEY (id)	FOREIGN KEY (parent_id) REFERENCES parent(id)
PRIMARY KEY (student_id, course_id)	FOREIGN KEY (student_id, course_id) REFERENCES enrollments(student_id, course_id)

Command

sql

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

CREATE TABLE enrollment_notes (
    id SERIAL PRIMARY KEY,
    student_id INT,
    course_id INT,
    note TEXT,
    FOREIGN KEY (student_id, course_id) REFERENCES enrollments(student_id, course_id)
);

A composite foreign key references a composite primary/unique key as one unit — the referencing table must supply the exact combination, not match either column independently, and the referenced column set must genuinely be unique on the target table.

5.ON DELETE Options and Their Operational Impact

The operational question behind ON DELETE is: "if this parent row disappears in production, what should actually happen to the data that depended on it?" CASCADE can silently delete far more than expected if the relationship chain is deep. RESTRICT blocks the delete outright, which is safe but can surprise an operator trying to clean something up. SET NULL and SET DEFAULT preserve the child row but change what it means.

Key features

CASCADE deletes ripple through every level of a foreign key chain automatically — a deep chain can delete far more than the immediate child table
RESTRICT (and the default NO ACTION) require dependents to be handled explicitly before a parent can be deleted — safer, but can block a routine cleanup unexpectedly
SET NULL requires the foreign key column to be nullable — it cannot be combined with NOT NULL on that column
SET DEFAULT requires the foreign key column to have a DEFAULT value that itself refers to a valid row (or NULL, if nullable)
A CASCADE chain's full blast radius is not visible from any single table's definition — it requires tracing every foreign key referencing that table, and everything referencing those

Operational risk profile of each ON DELETE option

Operational risk profile of each ON DELETE option
Option	Operational risk
CASCADE	can silently delete a large, non-obvious blast radius across chained tables
RESTRICT / NO ACTION	blocks the delete outright — safe, but can surprise an operator mid-cleanup
SET NULL	child survives but loses its reference — later queries must handle the NULL
SET DEFAULT	child survives but silently reassigns to a fallback — can misattribute data if not carefully chosen

Command

sql

CREATE TABLE orders (id SERIAL PRIMARY KEY);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE item_refunds (id SERIAL PRIMARY KEY, order_item_id INT REFERENCES order_items(id) ON DELETE CASCADE);

ON DELETE CASCADE composes across every level of a foreign key chain — its true blast radius is a property of the whole schema's reference graph, not just the one constraint being written, and is not visible from any single table's definition.

6.Detecting Dangerous Cascading Deletes in Production Schemas

PostgreSQL's system catalog pg_constraint records every foreign key and its confdeltype (the ON DELETE action). A recursive query over it can trace the full CASCADE chain starting from any table — revealing the true blast radius of deleting from that table before it happens in production, not after.

Key features

pg_constraint.confdeltype records the ON DELETE action: 'c' = CASCADE, 'r' = RESTRICT, 'n' = SET NULL, 'd' = SET DEFAULT, 'a' = NO ACTION
confrelid is the referenced (parent) table; conrelid is the referencing (child) table
A recursive CTE over pg_constraint, following confrelid, traces a table's full downstream CASCADE chain
A schema with no cyclic foreign keys (the normal case) means this recursive trace terminates naturally, like any other hierarchy walk
This is worth running before any production DELETE against a table with many known dependents, and periodically as a standing schema audit

pg_constraint columns relevant to a CASCADE audit

pg_constraint columns relevant to a CASCADE audit
Column	Meaning
conrelid	the table the constraint is defined ON (the child)
confrelid	the table being referenced (the parent)
confdeltype	the ON DELETE action — 'c' means CASCADE

Auditing a CASCADE chain before it deletes something in production

pg_constraint

every FK + its ON DELETE action

Recursive trace

follow confrelid downward, level by level

Full CASCADE chain

every table transitively affected

Decide before deleting

is this blast radius actually intended?

Command

sql

WITH RECURSIVE cascade_chain AS (
    SELECT conrelid::regclass AS child, confrelid::regclass AS parent, confdeltype
    FROM pg_constraint
    WHERE contype = 'f' AND confrelid = 'orders'::regclass AND confdeltype = 'c'
    UNION ALL
    SELECT c.conrelid::regclass, c.confrelid::regclass, c.confdeltype
    FROM pg_constraint c
    JOIN cascade_chain cc ON c.confrelid = cc.child
    WHERE c.contype = 'f' AND c.confdeltype = 'c'
)
SELECT * FROM cascade_chain;

A single table's definition cannot show what references it — trace the true CASCADE blast radius with a recursive query over pg_constraint (following confrelid, filtering confdeltype = 'c'), the same recursive pattern used to walk any hierarchy, before relying on a possibly-stale schema diagram.

Spotted a mistake on this page? Tell us.

Read Sheet