# Relationships

## 1. The 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)
```

## 2. One-to-Many: the Default Shape

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

```sql
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).

## 3. One-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:

```sql
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.

## 4. Many-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*:

```sql
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:

```sql
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
);
```

## 5. Self-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.

```sql
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:

```sql
-- 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.

## 6. Optional 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:

```sql
-- 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.

## 7. Referential 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.

```sql
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
);
```

| Action | Behavior when the referenced row is deleted |
|---|---|
| `RESTRICT` (default in most engines, or `NO ACTION`) | Delete is rejected outright if any child row references it |
| `CASCADE` | Every child row referencing it is deleted too, automatically |
| `SET NULL` | The child's FK column is set to `NULL` (requires the FK column to be nullable) |
| `SET DEFAULT` | The child's FK column resets to its declared default value |

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.

## 8. Under the Hood

![Adjacency list vs closure table for a self-referential category hierarchy: write cost vs descendant-query cost](diagrams/01-hierarchy-models.svg)

### 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.

## 9. 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.

## 10. 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
```