Relationships

1The Three Shapes, Restated Precisely

02-schema_design_and_data_modeling/ introduced cardinality at the modeling level. This topic is about how each shape is actually implemented, enforced, and queried — including the shapes that are easy to get subtly wrong: self-referential relationships, optional vs. required relationships, and what happens on delete.

One-to-Many (1:N):    department 1 ────────< N   employee
One-to-One (1:1):     employee   1 ────────1     employee_profile
Many-to-Many (N:M):   student    N >──────< M     course   (via enrolment)

2One-to-Many: the Default Shape

The foreign key always lives on the "many" side, pointing at the "one" side's primary key.

CREATE TABLE department (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employee (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  department_id INT REFERENCES department(id)   -- the "many" side holds the FK
);

A department row has no column pointing at its employees — "one department has many employees" is answered by a query (SELECT * FROM employee WHERE department_id = ?), not by a stored list. This is the fundamental asymmetry of the relational model versus a document store's embedded-array instinct (see 05-json_in_sql_and_nosql/ for when embedding is actually the right call).

3One-to-One: Two Legitimate Implementations

Split table (two tables, FK carries a UNIQUE constraint) — used when the second table represents optional, rarely-accessed, or logically separate data:

CREATE TABLE employee (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employee_profile (
  employee_id INT PRIMARY KEY REFERENCES employee(id),  -- PK doubling as the FK
  bio         TEXT,
  avatar_url  TEXT
);

Making employee_id both the primary key and the foreign key of employee_profile is what makes this genuinely 1:1 rather than 1:N — a normal FK column would allow multiple profile rows per employee; making it the PK forbids that by construction.

Merged table — when the "second entity" is never queried independently and always needed alongside the first, just add the columns to the same table. Splitting employee and employee_profile only pays off when profile data is large, optional, or updated at a very different rate than the core row (recall the TOAST discussion in 02-schema_design_and_data_modeling/, section 8) — otherwise the split is pure overhead, an extra join for data you always need together anyway.

4Many-to-Many: the Junction Table

A direct N:M relationship cannot be stored as a single foreign key on either side — an employee row can't hold "multiple department IDs" without violating 1NF. The fix is a third table holding one row per pairing:

CREATE TABLE student (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE course  (code TEXT PRIMARY KEY, title TEXT NOT NULL);

CREATE TABLE enrolment (
  student_id  INT  REFERENCES student(id),
  course_code TEXT REFERENCES course(code),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  grade       TEXT,
  PRIMARY KEY (student_id, course_code)   -- composite PK = the pairing IS the identity
);

Any attribute that describes the pairing itself (enrolled_at, grade) belongs on the junction table — it cannot correctly live on student (which student's grade in which course?) or on course (same problem in reverse). If the junction table needs to be referenced by yet another table (e.g. a certificate issued for a specific enrolment), give it its own surrogate key instead of relying purely on the composite:

CREATE TABLE enrolment (
  id          SERIAL PRIMARY KEY,       -- now referenceable on its own
  student_id  INT  REFERENCES student(id),
  course_code TEXT REFERENCES course(code),
  UNIQUE (student_id, course_code)       -- the pairing is still enforced unique
);

5Self-Referential Relationships

An entity relates to another row of the same entity — an employee's manager, a category's parent category, a comment's parent comment.

CREATE TABLE employee (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INT REFERENCES employee(id)   -- points back into the same table
);

This is a 1:N relationship exactly like any other (one manager, many direct reports) — the only difference is both ends live in the same table, which means recursive queries are needed to walk more than one level:

-- everyone in Ana's reporting chain, any depth, via a recursive CTE
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id FROM employee WHERE name = 'Ana'
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employee e JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;

An adjacency list (the manager_id column above) is simple to write to but expensive to query for "all descendants" at arbitrary depth — each level costs another recursive step. A closure table (a separate table pre-storing every ancestor-descendant pair, not just direct ones) trades write cost (every insert/move must update potentially many closure rows) for O(1)-join descendant/ancestor queries at any depth. Choose adjacency list when reads are shallow (one level: "who reports to X") or rare; choose a closure table when deep-hierarchy queries ("everyone under X, any depth") are frequent and the hierarchy doesn't restructure often.

6Optional vs. Required Relationships

A foreign key column that's nullable models an optional relationship; NOT NULL models a required one. This is a real modeling decision, not a formality:

-- Required: every order MUST have a customer — cannot exist orphaned
customer_id INT NOT NULL REFERENCES customer(id)

-- Optional: a support ticket MAY be assigned to an agent, or sit unassigned
assigned_agent_id INT REFERENCES agent(id)   -- nullable

Getting this wrong in either direction causes real bugs: making a required relationship nullable lets orphaned rows silently accumulate (a payment with no order); making an optional relationship required forces inserting a placeholder or sentinel row just to satisfy the constraint, which is worse than the null it was avoiding.

7Referential Actions: What Happens on Update/Delete

A foreign key isn't just a validation rule for inserts — it also has to say what happens when the referenced row changes or disappears.

CREATE TABLE order_item (
  order_id   INT REFERENCES "order"(id) ON DELETE CASCADE,
  product_id INT REFERENCES product(id) ON DELETE RESTRICT,
  quantity   INT NOT NULL
);

Choosing between them is a real design decision: order_item ON DELETE CASCADE from order makes sense — an order line item has no meaning once its order is gone. order_item ON DELETE RESTRICT from product is deliberately the opposite — deleting a product that has ever been ordered should fail loudly, not silently erase historical order records; the correct operation there is usually "deactivate the product," not delete it.

Under the HoodHow it works internally▾

CASCADE is a graph walk, not a single operation

ON DELETE CASCADE is not a bulk DELETE ... WHERE fk = x dressed up — the engine performs a recursive graph traversal at delete time. Deleting a department row with ON DELETE CASCADE on employee.department_id triggers a search for every employee row referencing it; if employee.id is itself referenced by, say, timesheet.employee_id with its own ON DELETE CASCADE, the engine must cascade again, transitively, until no more dependent rows exist anywhere in the FK graph. Two consequences follow directly from this being a graph walk rather than a flat operation:

1. Lock scope grows with the fan-out of the graph, not with the size of your original DELETE statement. Deleting one department row can escalate into row locks across three or four tables, several levels deep, all held for the duration of the same transaction.
2. Deadlock risk rises with cascade depth, because two concurrent transactions deleting different top-level rows can end up needing locks on overlapping descendant rows in a different order — the same fundamental deadlock shape as any multi-table lock ordering problem, just made likely by a single DELETE statement fanning out into many implicit ones.

This is precisely why production schemas often prefer RESTRICT plus an explicit, application-level, ordered deletion (or a soft-delete flag from 02-schema_design_and_data_modeling/, section 6) over deep CASCADE chains: predictable lock scope beats convenience once the FK graph gets more than one or two levels deep.

Self-referential recursive queries cost real work per level

A recursive CTE walking an adjacency list (section 5) is not free per level — each iteration is a genuine join against the base table, and the engine cannot generally use a single index range scan to answer "all descendants of X, any depth," because depth isn't encoded in the data at all. This is the mechanical reason a closure table trades write amplification (every insert or re-parenting of a node touches every affected ancestor-descendant pair) for read simplicity (a descendant query becomes one indexed lookup, not N recursive joins) — it's the exact same normalize-for-writes-vs-denormalize-for-reads trade-off from 01-normalization_and_denormalization/, applied specifically to hierarchical data.

Why a required (NOT NULL) FK is checked differently from an optional one

Both required and optional foreign keys are validated the same way on insert/update of the child row (the engine looks up the referenced value in the parent's primary key index — the full mechanics are in 04-primary_foreign_keys_constraints/). The difference only shows up on the parent delete/update path: an optional (NULLABLE) FK with ON DELETE SET NULL still requires the engine to find and rewrite every referencing child row (an UPDATE, not a no-op) — nullable does not mean "the engine can skip checking it," it only changes what the referencing rows become afterward.

⚠️ Common Pitfalls

1. Modeling N:M as two separate 1:N foreign keys on one of the entities — impossible without violating 1NF (a column can't hold multiple values); the junction table is not optional scaffolding, it's the only correct representation.
2. Putting a relationship's own attribute on the wrong table — grade cannot live on student or course alone; it describes the pairing, so it belongs on the junction table (or is lost the moment a student takes two courses).
3. Choosing CASCADE reflexively for convenience — a deep cascade chain silently deletes historical data (e.g. cascading order deletion into payment records) and, per the "Under the Hood" section, expands lock scope and deadlock surface in ways that aren't visible from reading the single DELETE statement that triggers it.
4. Using an adjacency list and then writing "get all descendants, any depth" queries constantly — each such query is a multi-step recursive join; if this access pattern is frequent, a closure table (or a materialized path column) is the appropriate trade, not a "smarter" recursive query.
5. Making a relationship nullable "just in case" — an optional FK that should be required lets orphaned data accumulate silently, and the bug surfaces far from where the bad row was created.
6. Forgetting that SET NULL requires the FK column to already be nullable — declaring ON DELETE SET NULL on a NOT NULL column is a contradiction the engine will reject at DDL time (or, in engines that don't check eagerly, will fail at delete time), not a bug in the code that triggers the delete.

📋 Quick Reference

1:N   FK lives on the "many" side, referencing the "one" side's PK
1:1   FK on the child, marked UNIQUE (or the FK IS the child's own PK)
N:M   junction table, composite PK (or surrogate PK + UNIQUE), FK to each side
       -> pairing's own attributes (grade, enrolled_at) live HERE

Self-referential: FK column referencing the same table
  adjacency list   -> cheap writes, recursive query needed per depth level
  closure table    -> write cost scales with hierarchy size, O(1)-join reads at any depth

Optional relationship -> nullable FK      Required relationship -> NOT NULL FK

ON DELETE RESTRICT   reject delete if children exist         (default; safest)
ON DELETE CASCADE    delete children too, recursively         (real data loss risk + lock fan-out)
ON DELETE SET NULL   children's FK -> NULL                    (FK column must be nullable)
ON DELETE SET DEFAULT children's FK -> declared default value

CASCADE = a graph walk: lock scope and deadlock risk scale with FK graph depth, not statement size

🎤 Interview Q&A

🛠️ Practice exercises

Each exercise is a file in this topic folder. Code exercises are self-checking: run them and make the checks pass.

✅ Check yourself

Source: dbmodel_relationships_theory.md · generated by tools/build_lessons.py