Unit 2 · Module 3

Foreign Keys In Depth

What they actually are, and the surprising fact that they don't have to point at a primary key

What a foreign key actually is

A foreign key is two things at once: a column that holds a pointer value, and a constraint that the database enforces on that column. Both halves matter, and the constraint half is the part that usually gets glossed over.

The column is the obvious piece. posts.user_id holds a UUID that matches some row's id in the users table. That's the pointer.

The constraint is the rule the database actively enforces: the value in posts.user_id must exist in users.id at the moment the post row is written. If it doesn't, the database refuses the insert. That refusal is what makes a foreign key different from a regular column that happens to hold an id.

A column vs. a foreign key

-- This is just a column holding a UUID. No enforcement.
CREATE TABLE posts (
  id      UUID PRIMARY KEY,
  user_id UUID
);

-- This is a foreign key. The database enforces that user_id
-- must point at a real users row.
CREATE TABLE posts (
  id      UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id)
);

What the constraint prevents — three concrete examples

The value of the constraint becomes obvious by looking at what it stops from happening.

1. Inserts with bad references

Trying to create a post with a user_id that doesn't match any actual user. Without the FK, the row goes in and now there's a post pointing at nothing. With the FK, the database refuses the insert with an error like violates foreign key constraint.

2. Deletes that orphan rows

Trying to delete a user who still has posts. Without the FK, the user disappears and the posts are orphaned — still in the database, still pointing at the now-missing user. With the FK, the database refuses the delete (or cascades it, depending on configuration), because letting the orphan happen would silently corrupt the data.

3. Updates that change the target

Changing a user's id. Without the FK, the change happens, every post that was pointing at the old id is now pointing at nothing, and nobody finds out for weeks. With the FK, the database either refuses or cascades the change so the posts stay consistent.

The lesser-known fact: foreign keys don't have to point at a primary key

Almost every example in every SQL tutorial points foreign keys at the referenced table's id column. That's the convention, not the rule. The actual rule is more flexible: a foreign key can point at any column with a UNIQUE constraint. Primary keys are unique, so they qualify. But other unique columns qualify too.

Foreign key on a non-primary-key column

CREATE TABLE countries (
  id           UUID PRIMARY KEY,
  iso_code     TEXT UNIQUE NOT NULL,   -- 'US', 'CA', 'MX', etc.
  name         TEXT NOT NULL
);

CREATE TABLE shipments (
  id              UUID PRIMARY KEY,
  destination_iso TEXT REFERENCES countries(iso_code)
);

The shipments.destination_iso column is a foreign key, but it references countries.iso_code instead of countries.id. That works because iso_code has its own UNIQUE constraint, which is the underlying rule a FK target actually needs.

When this is the right move

A few situations where pointing at a non-id unique column is genuinely better than the standard id pattern:

When it's a trap

The danger is pointing at a column that can change. Email addresses are unique, but people change their email. Usernames are unique, but people rename. If a foreign key points at one of these and the target changes, every referencing row either breaks or needs a cascading update — which works, but is more expensive than the standard pattern, and easier to get wrong.

How this connects to UI patterns you may already use

A pattern that comes up in real apps: tables get an extra column called display_name (or label, or slug) — a human-readable string for use in dropdowns, search results, and admin interfaces. The id is still what other tables foreign-key to, but the display_name is what shows up on screen.

That's a different tool than the non-PK foreign key, and the distinction is worth keeping clean:

Both are legitimate. They're answering different questions. The non-PK FK asks "what is the canonical identifier for this kind of thing?" — and answers it with something other than a UUID. The display_name column asks "what label should we show humans when we already foreign-key on id?" — and answers it with a presentation field.

When a join table is hard to read — the view trick

A common moment of frustration: opening a join table in the Supabase dashboard and seeing nothing but columns of foreign-key ids. race_id: 27, candidate_id: 39, status: 'announced'. The row is technically correct, but a human reading it has to do three lookups to know what it means.

The instinct is to "fix" this by making the foreign keys point at name columns instead of ids. That's almost always the wrong move — names usually aren't unique or stable enough to be safe FK targets. The right move is a view: a virtual table that runs a query and presents the result as if it were a table.

A readable view over a join table

CREATE VIEW race_candidates_readable AS
SELECT
  rc.id,
  r.name AS race,
  c.name AS candidate,
  rc.status,
  rc.is_incumbent
FROM race_candidates rc
JOIN races      r ON rc.race_id      = r.id
JOIN candidates c ON rc.candidate_id = c.id;

Now querying race_candidates_readable returns the readable version every time. The underlying table keeps its integer foreign keys (correct, stable, fast). The view is a separate object whose only job is presentation. Views can also be queried in the Supabase dashboard, so poking around in the database becomes possible without the FK soup.

Most join-table readability problems are solved by a view. Reach for the other two only when the view isn't enough.

ON DELETE and ON UPDATE — what happens to the references

When a foreign key exists, the database needs a rule for what to do when the referenced row is deleted or its key is changed. Postgres has a few options, written as part of the FK definition.

Common ON DELETE rules

-- Default: prevent the delete entirely.
user_id UUID REFERENCES users(id)

-- Cascade: delete dependent rows automatically.
user_id UUID REFERENCES users(id) ON DELETE CASCADE

-- Set null: keep the dependent row, blank out the FK.
user_id UUID REFERENCES users(id) ON DELETE SET NULL

Each one is the right answer in different situations. A user being deleted might mean their posts should be deleted too (CASCADE) — or that their posts should stay around but be marked authorless (SET NULL) — or that the delete shouldn't be allowed unless the posts are cleaned up first (default).

The choice is product-specific. Comments belong to a post and should probably cascade. Orders belong to a customer but should probably not cascade, because deleting a customer who made orders erases financial history.

What this page set up

A foreign key is a column plus a constraint. The constraint is what makes it valuable. Foreign keys usually point at primary keys, but the underlying rule is "any column with a UNIQUE constraint," which opens up natural keys and external system ids as legitimate FK targets when the target value is immutable. And every FK has an ON DELETE behavior that determines what happens when the referenced row goes away.

The next page steps back from the mechanics and looks at a whole schema as a map — how to read one for the first time and figure out what kind of system it represents.