Primary & Foreign Keys
You'll learn to
- -Choose a primary key (natural vs. surrogate) and enforce referential integrity with foreign keys
- -Explain what a foreign key actually prevents at the database level, not just what it documents
A primary key uniquely identifies each row in a table, and every other table that needs to reference that row does so through a foreign key - a column whose value must match a primary key value in the referenced table. Together, they are what makes relationships in the previous chapter enforceable by the database itself, not just documented in a diagram.
Natural Keys vs. Surrogate Keys
A natural key is an attribute that is already unique in the real world - a passport number, an email address. A surrogate key is an artificial identifier the database generates purely for identification, with no real-world meaning - a plain auto-incrementing integer or UUID. The strong default in almost every schema design is a surrogate key: natural keys have a habit of turning out not to be as unique or as permanent as they seemed (a passport number can be reissued; an email can change owners), and a surrogate key never needs to change even if every other fact about the row changes.
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 documentation of intent - it is a constraint the database actively enforces on every write. Attempting to insert a cabin with a passenger_id that does not exist in passengers.id fails at the database level, before any application code even runs. Attempting to delete a passenger who is still referenced by a cabin also fails by default (or cascades/nullifies, depending on the ON DELETE behavior chosen), which is exactly the guarantee that keeps a schema from silently accumulating orphaned rows that point at nothing.
ON DELETE Behavior Is a Real Design Decision
ON DELETE RESTRICT (the default in most databases) blocks the delete entirely if references exist. ON DELETE CASCADE deletes the referencing rows automatically. ON DELETE SET NULL nulls out the foreign key instead of deleting anything. These are not interchangeable defaults - deleting a passenger should probably not silently cascade-delete their cabin record without an explicit decision that this is the desired business behavior, which is exactly the kind of choice worth naming out loud rather than leaving as an unexamined default.
A foreign key column pointing at a table that does not enforce its own primary key uniqueness is a silent correctness bug waiting to happen - always define the primary key on the referenced side first, and let the database, not application code, 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.
Design Crew Departments in the LLD Lab's Titanic act.