Foreign Keys: Tables That Point at Each Other

A screening is "Night Bus, screen 1, Friday at 20:00" — but look back at the screenings table from two pages ago and you won't find the words "Night Bus" anywhere in it. The table stores film_id 3: a pointer to the row in films that already knows everything about Night Bus. That pointing column is a foreign key — a column in one table whose values must exist as primary key values in another.

This is the page where the course's title cashes in. "Relational" has been a promissory word since Chapter 1 — facts point at each other, we said, and the pointing is the information. The foreign key is that pointing, made into a declared, enforced rule. Everything from here to the end of the book stands on it.

Store the Fact Once, Point at It Everywhere

Why not just copy the title into each screening row? It would certainly be easier to read. But Night Bus screens eleven times this month — copying means the title now lives in eleven places, plus the one in films. The day Lora fixes a typo in the title, she must fix it twelve times, and if she misses one, the database now contains two spellings of the same film and no way to tell which is right. One fact stored in many places will eventually disagree with itself.

Pointing inverts the arithmetic. The title lives in exactly one row of films; every screening carries only the small, meaningless film_id that names that row. Fix the typo once, and every screening is instantly "fixed," because none of them ever held the title — they held directions to it. Think of a library catalog card: it cites the book's shelf code rather than re-copying the book, and when the book moves — or gets a corrected cover — every card citing the code is automatically right. Mapped; back to foreign keys.

Declaring the Pointer

Here is screenings again, now with both kinds of key declared — its own primary key, and the foreign key pointing at films:

CREATE TABLE screenings (
  screening_id  INTEGER PRIMARY KEY,
  film_id       INTEGER REFERENCES films(film_id),
  screen        INTEGER,
  starts_at     TIMESTAMP,
  price         NUMERIC(6,2)
);

The new clause reads as a sentence: film_id REFERENCES films(film_id) — "values in this column must exist in the film_id column of the films table." With that one line, the database takes on a new enforcement duty. A pointer aimed at a row that doesn't exist is called a dangling pointer, and the database now refuses to ever let one into the building:

INSERT INTO screenings (screening_id, film_id, screen, starts_at, price)
VALUES (15, 99, 2, '2026-07-18 17:30', 11.50);

The Marquee has three films, with ids 1, 2, and 3. This INSERT points its film_id at 99 — no such film — and the database hands it straight back: the reference cannot be satisfied, so nothing is stored. "The database says no," a third time this chapter, now guarding the connections between tables. A spreadsheet, for comparison, will happily let a bookings row cite a showtime that was deleted last week.

One-to-Many, and Which Side Points

The picture above has a shape worth naming. One film has many screenings; each screening belongs to exactly one film. That shape is called a one-to-many relationship, and it is the most common arrangement in every database you'll ever open. Notice where the foreign key lives: on the many side, always. Each screening carries one film_id; the film carries nothing about screenings at all — the many rows point at the one, never the reverse.

Practice reading the direction out loud, because it trips everyone at first: "screenings.film_id points at films.film_id" — the column lives in screenings, its values must exist in films. When you meet an unfamiliar schema, finding the foreign keys and saying each one aloud this way is how professionals actually read it: many screenings per film, many bookings per customer, many orders per shopper.

What Deletion Now Means

One consequence arrives with the pointer, and honesty requires naming it now. Suppose Lora tries to delete Night Bus from films while three screenings still point at it. The database objects — deleting the row would turn three valid pointers into dangling ones, the exact thing it promised to prevent. By default, the deletion is simply refused until the screenings are dealt with.

There are options — the schema can be told to forbid such deletions outright, or to cascade them, deleting the pointing rows along with the pointed-at one. Choosing between those is a real design conversation, and it belongs to Chapter 6; this page only flags that the choice exists. For now, the default behavior is the right instinct: connected data doesn't quietly disappear.