Skip to content
LLD Learn/Relational Modeling Foundations
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Primary & Foreign Keys

5 min read

You'll learn to

  • -Explain a primary key as a row's permanent ID and a foreign key as a reference to that ID
  • -Choose between a natural key and a surrogate key, and say why surrogate is the usual default
  • -Pick ON DELETE RESTRICT, CASCADE or SET NULL by asking what should happen to the other rows

A primary key is a column, or set of columns, whose value uniquely identifies each row in a table. Every other table that needs to point at that row does so with a foreign key, a column whose value must match a primary key in the referenced table. Together they make the relationships from the previous chapter something the database enforces itself, not just something written on a diagram.

An Analogy: The Student Number

A university has two students called Anna Lee. Names repeat, change after a marriage, and get misspelled. So every student gets a student number, and when a form needs to refer to a student it writes the number, never a copy of their details. The student number is a primary key. The number written on the form is a foreign key. If the university changes Anna's address, it changes it in exactly one place.

Natural Keys vs. Surrogate Keys

A natural key is an attribute that is already unique in the real world, such as a passport number or an email address. A surrogate key is an artificial identifier the database creates only to identify the row, with no real-world meaning. Common choices are an auto-incrementing integer, or a UUID (universally unique identifier), a long random-looking label that is practically unique. A surrogate key is the strong default. Natural keys turn out less unique and less permanent than they seem: a passport can be reissued with a new number, and an email address can change hands. A surrogate key never has to change, even if every other fact about the row does.

A surrogate primary key, referenced by a foreign key
CREATE TABLE passengers (
    id INTEGER PRIMARY KEY,          -- surrogate key: meaningless, stable identifier
    first_name TEXT NOT NULL
);

CREATE TABLE cabins (
    id INTEGER PRIMARY KEY,
    cabin_number TEXT NOT NULL,
    passenger_id INTEGER UNIQUE,     -- foreign key: must match a real passengers.id
    FOREIGN KEY (passenger_id) REFERENCES passengers(id)
);

What a Foreign Key Actually Enforces

A foreign key is not just a comment about intent. The database checks it on every write. Say the passengers table has ids 1 to 5. Inserting a cabin whose passenger_id is 999 fails at once, before any application code notices, because passenger 999 does not exist. Deleting passenger 3 while a cabin still points at them fails too, unless you chose otherwise. This is what stops a schema from silently collecting orphaned rows that point at nothing.

ON DELETE Is a Real Design Decision

Suppose passenger 7 holds a cabin, and the passenger is deleted. What should happen to the cabin row? The three options give three very different results.

  • -ON DELETE RESTRICT (or NO ACTION, the usual default) blocks the delete while anything still points at passenger 7. Nothing is lost, and someone must free the cabin first.
  • -ON DELETE CASCADE deletes every row that points at the deleted one. Here the cabin row itself would vanish from the ship's records, which is almost certainly wrong, because the cabin still exists.
  • -ON DELETE SET NULL keeps the cabin row and blanks its passenger_id. The cabin simply becomes unassigned, which is usually the right behavior here.

These are not interchangeable defaults. The right choice depends on what the business wants, so name it aloud instead of leaving an unexamined default.

A foreign key that points at a table which does not enforce uniqueness on its primary key is a silent bug waiting to happen. Define the primary key on the referenced table first, and let the database, not the application, be the source of truth for referential integrity.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design Crew Departments in the LLD Lab's Titanic act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!