Keys — primary, candidate, super, foreign, natural vs surrogate: Visual walkthrough

Step 1 — A table is a set of rows (the starting picture)

WHAT. We begin with a tiny table Student. Each row is one student. The columns are roll_no, email, name, dob.

WHY. Before we hunt for keys we must agree on the one rule that makes keys necessary: a relational table is a set — no two rows may be identical, and rows have no order. If rows have no order and no built-in "row number", the only way to grab one specific row is by its data. That is the entire reason keys exist.

PICTURE. Four rows, four columns. Notice Ravi appears as a name twice (rows 1 and 4) — remember that, it matters in Step 3.

Figure — Keys — primary, candidate, super, foreign, natural vs surrogate

Step 2 — Super key: any $K$ that survives the uniqueness test

WHAT. We test several column-sets against the question from Step 1 and mark which ones give all-different values.

WHY. The most general, un-trimmed idea is: does this set guarantee uniqueness at all? We do not yet care if it carries junk columns. Anything that passes is a super key. This is the base of the ladder because it is the loosest condition — everything else is a special case of it.

PICTURE. Green ✅ = values all different (super key). Red ✗ = a collision exists (not a super key). Look at ${nam e}$ : rows 1 and 4 collide (both Ravi), so its column of values is not all-different → red.

Step 3 — Candidate key: chop off the fat until nothing can leave

WHAT. Take a super key like ${r o l l_n o, nam e}$ and try removing one column at a time. If a smaller subset still passes the Step-2 test, the bigger one was not minimal.

WHY. We want the honest smallest identifiers — no passengers. A candidate key is a super key where removing any single attribute breaks uniqueness. This trims the sloppy super-key family down to the tight, essential ones.

PICTURE. Left column: ${r o l l_n o, nam e}$ is a super key. Remove name → ${r o l l_n o}$ still all-different → so ${r o l l_n o, nam e}$ is redundant, disqualified as candidate (amber "not minimal"). Right column: ${r o l l_n o}$ — remove its only attribute and you have ${}$ (nothing), which cannot identify anything → it is minimal → candidate ✅.

Step 4 — Primary key: crown exactly one candidate

WHAT. Among the candidate keys ${r o l l_n o}$ and ${e mai l}$ , the designer picks one to be the official row-handle. We pick roll_no. The loser, email, becomes an alternate key.

WHY. A table needs one stable, guaranteed handle so other tables can point at it and indexes can be built once. Two rules make a candidate fit to be the boss: it must be NOT NULL (a NULL means "unknown", and "unknown" cannot pick out a row) and stable (it should never change, so links never break).

PICTURE. Two candidate keys side by side; a crown lands on roll_no (primary), and email gets a UNIQUE badge (alternate). A NULL slot on roll_no is drawn crossed out — forbidden.

Step 5 — Foreign key: borrow another table's handle

WHAT. A second table Enrolment needs to record which student enrolled. Instead of copying the student's whole data, it stores the student's roll_no — the primary key from Student. That borrowed column is a foreign key.

WHY. We split data across tables to avoid repeating a student's details in every enrolment row. The foreign key is the glue: it enforces referential integrity — you may only reference a roll_no that actually exists in Student.

PICTURE. Two tables. Arrows run from Enrolment.roll_no values into matching Student.roll_no rows. An amber arrow tries to point at roll_no = 999, finds no target, and is blocked.

Step 6 — Edge case: NULLs and the degenerate empty key

WHAT. We push two boundary cases. (a) What if a primary-key cell were NULL? (b) What is the "smallest possible" key — the empty set ${}$ ?

WHY. A derivation is only trustworthy if it covers the corners. Both corners are forbidden, and seeing why cements the definitions.

PICTURE. Left panel: a roll_no cell set to NULL (unknown) — two such rows are indistinguishable, so uniqueness collapses → primary key rejects NULL. Right panel: $K = {}$ maps every row to the same empty tuple, so all rows "collide" → the empty set is never a super key (unless the table has ≤ 1 row, a trivial degenerate case).

Step 7 — Natural vs Surrogate: the choice of what data to crown

WHAT. roll_no and email are natural keys (real-world meaning). We can instead add a student_id auto-increment column that means nothing — a surrogate key — and crown it as primary, keeping email behind a UNIQUE guard.

WHY. Natural data can change (email changes), be NULL (not everyone has one yet), or turn out non-unique. A surrogate is stable, compact, always present, and meaningless — so it never needs updating and never breaks foreign-key links. Cost: it is not human-readable, and you must still keep the natural column with UNIQUE so two real-world duplicates can't sneak in.

PICTURE. Same Student rows, now with an amber student_id column (1,2,3,4). The crown moves to student_id; email keeps its UNIQUE badge. A speech bubble: "email may change → id never does."

The one-picture summary

The whole ladder in a single frame: the loose Super set contracts to the minimal Candidate set, one candidate is crowned Primary, that primary is borrowed by another table as a Foreign key, and the data you choose for it is either Natural or Surrogate.

Recall Feynman retelling — the whole walkthrough in plain words

Picture a classroom that is a bag of kids with no order — no "kid #1", just kids. To point at one, I must describe them by facts.

Any wordy-but-unique description ("roll 7, wearing red") is a super key — extra words are harmless.
The shortest descriptions that still work ("roll 7") are candidate keys — drop a word and it fails.
We officially print one of them on every name tag — that's the primary key; it can't be blank, or the tag identifies nobody.
When the library logs who borrowed a book, it copies that roll number — a foreign key, borrowing the classroom's tag system, and it refuses roll numbers of kids who don't exist.
The tag can be a natural fact (their email, which might change) or a meaningless surrogate ticket number (which never changes but you can't read anything into it). Because facts drift, most designers hand out ticket numbers and also keep a "no two emails alike" rule on the side.

Recall Quick self-test

A set ${A, B}$ is unique and ${A}$ alone is also unique — is ${A, B}$ a candidate key? ::: No — it isn't minimal; ${A}$ is. ${A, B}$ is only a super key. Can a primary-key column hold NULL? ::: No — NULL means "unknown" and cannot identify a row. Why is the empty column-set ${}$ not a super key (for a 2+ row table)? ::: Every row maps to the same empty value, so all rows collide.

Connections

Relational Model — the "table is a set of rows" premise that starts Step 1
Functional Dependencies — the formal machinery behind "these columns determine the row"
Referential Integrity — the rule the foreign key of Step 5 enforces
Normalization — candidate keys from Step 3 drive 2NF/3NF/BCNF
Indexes — why the one primary key of Step 4 gets a physical structure built on it
Entity-Relationship Model — where natural vs surrogate identifiers are first chosen