Keys & joins: how tables point at each other

A table is a set of rows that share the same columns. Two kinds of key turn a pile of tables into a database:

• A primary key is the column (or columns) that names exactly one row. PostgreSQL enforces it: the value must be unique and not null, and declaring it builds a unique B-tree index.
• A foreign key is a column whose value must match a key in another table. It's how an order says "I belong to customer 1" — and how the database refuses an order for a customer that doesn't exist.

CREATE TABLE customers (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  total       numeric(10,2) NOT NULL
);

GENERATED ALWAYS AS IDENTITY makes the database hand out the ids (1, 2, 3…). REFERENCES customers (id) is the foreign key. From now on the database guards the relationship from both ends:

INSERT INTO orders (customer_id, total) VALUES (42, 10);
-- ERROR: insert or update on table "orders" violates foreign key
--        constraint "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;   -- customer 1 still has orders
-- ERROR: update or delete on table "customers" violates foreign key
--        constraint "orders_customer_id_fkey" on table "orders"

This is a one-to-many relationship: one customer, many orders. The foreign key lives on the "many" side — each order holds one customer_id; the customer row holds no list of orders. "All of customer 1's orders" is a query (WHERE customer_id = 1), not a column.

Why let the database check this rather than your application code? Because every writer goes through the same check — your API, a one-off script, a teammate in the SQL editor. A check in application code guards only the code paths that remember to call it.

Users belong to teams; a user can be in many teams and a team has many users. Teams also have invoices and tasks.

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE teams (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE team_members (
  team_id bigint NOT NULL REFERENCES teams (id) ON DELETE CASCADE,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  role    text   NOT NULL DEFAULT 'member',
  PRIMARY KEY (team_id, user_id)
);
CREATE INDEX ON team_members (user_id);

CREATE TABLE tasks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id     bigint NOT NULL REFERENCES teams (id) ON DELETE CASCADE,
  assignee_id bigint REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE invoices (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id bigint NOT NULL REFERENCES teams (id) ON DELETE RESTRICT
);

A surrogate id, and email as UNIQUE. Email is unique, so it could be the primary key — a natural key. But emails change, and every foreign key stores a copy of the key it points at. Changing a referenced email is refused by default; ON UPDATE CASCADE would allow it by rewriting every referencing row. A meaningless bigint never needs to change. The UNIQUE constraint still guarantees one account per email.

GENERATED ALWAYS, not BY DEFAULT. With ALWAYS, an INSERT that supplies an id is rejected unless it says OVERRIDING SYSTEM VALUE. That stops a seed script from writing id 100 by hand, after which the sequence eventually hands out 100 again and the insert fails.

The many-to-many lives in its own table. Neither users nor teams can hold the relationship, so team_members holds one row per link, with a foreign key to each side. The composite PRIMARY KEY (team_id, user_id) makes adding Ada to the same team twice an error (23505 duplicate key) instead of a silent duplicate. The link can carry its own data — role.

The extra index on user_id. The primary key's index is ordered by team_id first, so it serves "who is in team 7?" but not "which teams is user 1 in?". PostgreSQL does not create an index for a foreign key's referencing column, and deleting a user makes it look up that user's memberships. Index the referencing side yourself.

One delete rule per relationship, chosen by asking "what should happen to the child?"

• CASCADE — the child means nothing without the parent: a membership of a deleted team.
• SET NULL — the child should survive, unowned: a task whose assignee left stays on the board, unassigned. (The column must be nullable for this.)
• RESTRICT — deleting the parent should be a decision, not a side effect: a team with invoices can't be deleted until someone deals with the billing history.
• NO ACTION — the default when you write nothing. Like RESTRICT it refuses the delete; the difference is that its check can be deferred to the end of the transaction when the constraint is declared DEFERRABLE, and RESTRICT's can't.

The rules compose. Deleting a team with invoices fails outright, and nothing cascades. Deleting a team without invoices removes its memberships and tasks in the same statement.

None of these raise an error. Each returns a plausible-looking result that's wrong.

1. A WHERE on the right table undoes the LEFT JOIN

"Every customer, with any orders over 20":

-- Wrong: Linus disappears
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 20;

-- Right: the filter decides which orders match, not which customers survive
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 20;

The PostgreSQL manual puts it exactly: a restriction in ON is processed before the join, a restriction in WHERE after it. After the join, Linus's row has o.total = NULL, and NULL > 20 isn't true, so WHERE throws him out — the outer join has silently become an inner join. With inner joins the two placements are equivalent; with outer joins they aren't.

2. NOT IN against a column that holds a NULL

"Users who haven't been invited":

SELECT email FROM users
WHERE id NOT IN (SELECT user_id FROM invites);   -- invites.user_id is nullable

One invite row with user_id = NULL (an invite sent to an email address that has no account yet) and this returns zero rows, for every user. 3 NOT IN (1, NULL) means 3 <> 1 AND 3 <> NULL. The second comparison is NULL (unknown), true AND NULL is NULL, and WHERE keeps only rows that are true. The manual says it directly: if there's no equal value and at least one NULL, NOT IN yields null, not true.

SELECT email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM invites i WHERE i.user_id = u.id);

EXISTS asks only "did the subquery return any rows?", so a NULL row can't poison it. Make NOT EXISTS the habit; NOT IN is safe only against a list you know can't contain NULL. (IN doesn't have this problem: a NULL just fails to match.)

3. Joining two children multiplies both

An order with 3 line items and 2 payments of 15:

SELECT o.id, sum(i.qty * i.price) AS item_total, sum(p.amount) AS paid
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN payments p    ON p.order_id = o.id
GROUP BY o.id;
-- item_total 50 (really 25), paid 90 (really 30)

The join produces one row per (item, payment) pair: 3 × 2 = 6 rows. Each item is counted twice and each payment three times. This is fan-out: a join to a "many" side repeats the row on the "one" side, and joining to two unrelated "many" sides multiplies them together. Aggregate each child on its own first, then join the one-row-per-order results:

SELECT o.id, i.item_total, p.paid
FROM orders o
LEFT JOIN (SELECT order_id, sum(qty * price) AS item_total
           FROM order_items GROUP BY order_id) i ON i.order_id = o.id
LEFT JOIN (SELECT order_id, sum(amount) AS paid
           FROM payments GROUP BY order_id) p    ON p.order_id = o.id;
-- item_total 25, paid 30

The tell for fan-out is a total that's a clean multiple of the real one. count(DISTINCT …) can paper over a doubled count, but there's no DISTINCT fix for a doubled sum — two equal payments are two payments.