Keys — primary, candidate, super, foreign, natural vs surrogate: Worked examples

The scenario matrix

Every question about keys is really one of these case classes. Think of it like the quadrants of a graph: we must visit each one.

#	Case class	The question being stressed	Covered by
C1	Single unique column	Is one attribute enough to be a key?	Ex 1
C2	Composite (multi-column) key	When is a bundle the smallest key?	Ex 2
C3	Super vs candidate trimming	Which supersets get disqualified?	Ex 3
C4	Degenerate: duplicate values	A "key" that isn't unique — what breaks?	Ex 4
C5	Degenerate: NULLs present	Can a column with blanks be a primary key?	Ex 5
C6	Foreign key repetition / NULL	May the FK column repeat or be empty?	Ex 6
C7	Limiting case: whole-row key	What if only all columns together are unique?	Ex 7
C8	Natural vs surrogate choice	Real-world word problem — which to pick?	Ex 8
C9	Exam twist: counting super keys	Given candidate keys, how many super keys exist?	Ex 9
C10	Exam twist: FK cascade behaviour	What happens on delete of a referenced row?	Ex 10

The figure below is the map of how the key-types nest — keep it in your eye while reading; every example lands somewhere inside these rings.

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

Intuition How to read the ring map (figure above)

The rings are nested on purpose — each smaller ring is a stricter version of the one around it, so anything inside a ring is automatically inside every larger ring too.

Outermost yellow ring — SUPER KEY. The loosest test: any unique set of columns, even with junk columns bolted on. Ex 3's {roll_no, name} lives here (unique, but padded).
Middle blue ring — CANDIDATE KEY. Only the super keys that are minimal survive inward. Ex 1's {aadhaar_no} and Ex 3's {roll_no}, {email} sit here.
Inner pink ring — PRIMARY KEY. Exactly one candidate key the designer promotes; the other candidates become alternate keys (the pink arrow points them out, still in the blue ring).
Detached box — FOREIGN KEY. It is not a ring at all: it sits outside the table and its arrow reaches into another table's candidate/primary ring (Ex 6, Ex 10). Because rings nest, the direction "candidate ⇒ super" always holds while "super ⇒ candidate" fails — exactly the parent's one-way hierarchy.

Table Person(aadhaar_no, name, city). Government guarantees every aadhaar_no is unique and always present. Two people can share a name and a city. Is {aadhaar_no} a candidate key? Is it a super key?

Forecast: guess before reading — is a single guaranteed-unique column automatically both?

Check super key. A super key is any attribute set whose values are unique across rows. aadhaar_no is unique → {aadhaar_no} is a super key. ✅ Why this step? Super key is the loosest test — we always start here because everything else is a special case of it.
Check minimality (for candidate). Can we remove an attribute and stay unique? The set has only one attribute; removing it leaves the empty set {}, which cannot identify anything. So it is already minimal. Why this step? Candidate = minimal super key. A single-column super key is automatically minimal because there is nothing smaller to drop.
Conclusion. {aadhaar_no} is a super key and a candidate key.

Verify: Minimal + unique ⇒ candidate, by definition. And every candidate key is a super key (parent's hierarchy line). Consistent. ✅

Example 2 — the composite key (cell C2)

Worked example Statement

Table Enrolment(student_id, course_id, grade). A student may take many courses; a course has many students; but a student takes a given course at most once. No single column is unique. Find the candidate key.

Forecast: which bundle of columns is the smallest that pins one row?

Reject single columns. student_id repeats (many courses), course_id repeats (many students), grade repeats (many A's). None alone is a super key. Why this step? We must show why the obvious small keys fail before reaching for a bigger set.
Try the pair {student_id, course_id}. "One enrolment per student-course" is exactly the rule that makes this pair unique. So it is a super key. Why this step? A composite key is needed precisely when the real-world uniqueness rule spans two facts at once.
Test minimality. Drop course_id → {student_id} repeats. Drop student_id → {course_id} repeats. Neither proper subset is unique → the pair is minimal. Why this step? Minimality is the line between super key and candidate key.
Conclusion. {student_id, course_id} is the candidate (and would-be primary) key. grade is not part of it.

Verify: Both single-column subsets fail uniqueness while the pair holds ⇒ minimal super key ⇒ candidate key. See Functional Dependencies: {student_id, course_id} → grade, and no smaller left-hand side determines the row. ✅

Example 3 — super keys that get chopped (cell C3)

Worked example Statement

Table Student(roll_no, email, name) with roll_no unique and email unique. Classify each set: {roll_no}, {email}, {roll_no, email}, {roll_no, name}, {name}.

Forecast: which of these are super keys, which are candidate keys, which are neither?

{roll_no} — unique → super key; single column → minimal → candidate key. ✅ Why this step? We test the given single-column keys first because a single unique column is the simplest possible candidate — no minimality work is needed once uniqueness is confirmed.
{email} — unique → super key; minimal → candidate key. ✅ Why this step? Same reasoning as roll_no: an independently guaranteed-unique single column is automatically a candidate key, and finding all candidate keys means checking each such column, not just one.
{roll_no, email} — unique (any superset of a unique set stays unique) → super key. But drop email → {roll_no} still unique → not minimal → not candidate. Why this step? Adding columns can never break uniqueness, so it stays a super key — but the extra column disqualifies it as a candidate.
{roll_no, name} — unique → super key; drop name → {roll_no} unique → not minimal → not candidate. Why this step? Shows the trap of padding a good key with a redundant descriptive column: still unique, still super, but no longer minimal.
{name} — two students may share a name → not even a super key. Why this step? Covers the failure floor: a set that isn't unique is nothing at all in this hierarchy.

Verify: Candidate keys found = {roll_no}, {email}. All others are super-key-only or non-keys — matches the parent's rule "every candidate is a super key, not vice-versa". ✅

Example 4 — the degenerate duplicate (cell C4)

Worked example Statement

Someone claims {phone} is the primary key of Contact(phone, name). But the data contains two rows: ('555-0101', 'Ann') and ('555-0101', 'Ann's landline shared with Bob'). What goes wrong, and is {phone} a key at all?

Forecast: if a "key" has a repeated value, is it still a key?

Apply the uniqueness test literally. Two rows share phone = '555-0101'. So reading phone does not point at exactly one row. Why this step? A key's whole job is "value ⇒ one row". A duplicate value breaks that at the source.
Conclusion on status. {phone} is not a super key here, hence not a candidate, hence cannot be the primary key. Why this step? The chain super → candidate → primary is broken at the first link.
What the DB does. If you had declared PRIMARY KEY (phone), the second INSERT is rejected — the engine refuses the duplicate. Why this step? This is the practical face of the theory: constraints enforce the definition.

Verify: Uniqueness fails ⇒ not a key. This is exactly mistake (a)-flavour reasoning from the parent: uniqueness is the requirement, and it is absent. ✅

Example 5 — NULLs in a would-be primary key (cell C5)

Worked example Statement

Employee(emp_id, ssn, name). Most employees have an ssn, but new interns do not yet — those rows have ssn = NULL. ssn values that do exist are all distinct. Can ssn be the primary key? Can it be an alternate key?

Forecast: distinct-where-present but blank sometimes — is that good enough for the "official" key?

Primary key rule. A primary key must be NOT NULL. A NULL means "unknown", and unknown cannot identify a row. Why this step? This is a definitional wall (parent mistake (b)) — no exception exists.
So ssn fails as primary key because interns' rows have no value to identify them by.
Alternate / UNIQUE possibility. A UNIQUE constraint allows NULLs (usually treating each NULL as distinct). So ssn UNIQUE is legal and prevents duplicate real SSNs. Why this step? Distinguishes the two SQL tools: PRIMARY KEY = unique + not null; UNIQUE = unique but NULL-tolerant.
Conclusion. Use a surrogate emp_id as primary key; keep ssn UNIQUE.

Verify: Primary needs unique and not-null; ssn has nulls ⇒ disqualified as primary but fine as UNIQUE. Matches parent §3 and §5. ✅

Example 6 — foreign key that repeats and goes NULL (cell C6)

Worked example Statement

Enrolment(enrol_id PK, roll_no FK → Student, course). Student 7 enrols in three courses; a "walk-in" trial enrolment has no student yet (roll_no = NULL). Is this table valid?

Forecast: three rows with roll_no = 7, plus a blank one — legal or not?

FK repetition. A foreign key column may hold duplicate values freely — one referenced row can be pointed to many times. Why this step? Corrects parent mistake (c): only the referenced key must be unique, never the FK column.
The three rows with roll_no = 7. Each is a different enrolment (enrol_id differs). All three point to the same existing student 7 → allowed.
The NULL FK. A NULL foreign key means "references nothing yet" — this is permitted; referential integrity only checks non-NULL values against the parent table. Why this step? Covers the degenerate FK input (empty reference) explicitly.
Conclusion. Fully valid, provided student 7 exists in Student.

Verify: FK duplicates allowed, FK NULL allowed, only non-NULL FK values must exist in the referenced key. Consistent with Referential Integrity. ✅

Example 7 — when only the whole row is unique (cell C7)

Worked example Statement

A dedupe worksheet Sighting(species, place, day) records bird sightings. The rules are loose: the same species can appear in many places, any place hosts many species, and days repeat. Crucially, even every pair can repeat — the same species can be seen in the same place on two different days and the same species on the same day in two different places and two different species in the same place on the same day. The only guarantee is that no two rows are identical in all three columns at once. Find the candidate key.

Forecast: if every single column and every pair repeats, what is left to identify a row?

Reject all single columns. species repeats, place repeats, day repeats. None alone is a super key. Why this step? We always climb from the smallest sets upward; ruling out size-1 sets is the first rung.
Reject all three pairs. {species, place} repeats (seen on two days), {species, day} repeats (two places), {place, day} repeats (two species). So no size-2 set is unique either. Why this step? This is the defining feature of the whole-row case: unlike Ex 2, uniqueness does not appear at the pair level, so we cannot stop here.
Try the full set {species, place, day}. The only stated guarantee is that no two rows match on all three columns → this set is unique → it is a super key. Why this step? When every proper subset repeats, the entire attribute set is the smallest thing that can still separate rows — we test it because nothing smaller survived.
Minimality — it is forced. Drop any one column and we fall back to a pair, which step 2 showed repeats. So no proper subset is a super key → the full three-column set is minimal. Why this step? Minimality here is automatic: every subset already failed, so removing anything breaks uniqueness by construction.
Conclusion. The candidate key is the whole row {species, place, day}. This is the limiting case where all columns together are needed — an all-key relation (every attribute is part of the only key).

Verify: All three singles and all three pairs repeat, only the triple is unique ⇒ the triple is the minimal super key ⇒ candidate key = whole row. This is the extreme opposite of Ex 1. ✅

Example 8 — natural vs surrogate, real-world word problem (cell C8)

Worked example Statement

You design Book. Each book has an ISBN (globally unique, 13 digits, assigned by publishers). Marketing warns: reprints sometimes get a new ISBN, and very old books have none. Choose the primary key and justify.

Forecast: is the "meaningful, official-looking" ISBN the right primary key?

Test ISBN as natural key. ISBN is a natural key: real-world, meaningful. But (a) some books have no ISBN → NULL; (b) reprints change it → not stable. Why this step? Primary keys must be not null and should never change (parent §3).
Both requirements fail. NULL breaks not-null; change breaks stability. So ISBN is a poor primary key.
Introduce a surrogate. A surrogate key book_id (auto-increment) is always present, meaningless, and never changes → satisfies both rules. Why this step? This is exactly why surrogate keys are popular (parent §5 intuition).
Keep the natural key too. Add isbn UNIQUE (NULL-tolerant) so real duplicate ISBNs are still blocked — avoiding parent mistake (d).
Conclusion. Primary key = surrogate book_id; isbn UNIQUE alongside.

Verify: Not-null + stable ⇒ surrogate wins; UNIQUE on ISBN preserves real-world integrity. Matches parent §5 and mistake (d). ✅

Example 9 — exam twist: counting super keys (cell C9)

Worked example Statement

A table R(A, B, C, D) has exactly one candidate key: {A}. How many super keys does R have?

Forecast: guess a number before computing — dozens? Or exactly how many?

Key insight. Any set of attributes is a super key iff it contains a candidate key. Here that means: a set is a super key iff it contains A. Why this step? A super key = candidate key + possibly extra attributes; since {A} is the only candidate key, "contains a candidate key" reduces to "contains A".
Count sets containing A. Fix A in. The other three attributes B, C, D are each independently in or out → $2^{3} = 8$ choices. Why this step? Each optional attribute doubles the count — this is the "in/out" counting of subsets.
Conclusion. There are $2^{3} = 8$ super keys.

Verify: With one candidate key of size 1 over 4 attributes, super keys $= 2^{4 - 1} = 8$ . (List: {A}, {A,B}, {A,C}, {A,D}, {A,B,C}, {A,B,D}, {A,C,D}, {A,B,C,D}.) ✅

Example 10 — exam twist: foreign key on delete (cell C10)

Worked example Statement

Student(roll_no PK) and Enrolment(enrol_id PK, roll_no FK → Student(roll_no), course, ON DELETE ...). Student 7 currently has 3 enrolment rows. Someone runs DELETE FROM Student WHERE roll_no = 7. What happens under each option: (default) RESTRICT, CASCADE, and SET NULL?

Forecast: does the delete succeed, fail, or ripple into the child table?

Default / RESTRICT. The 3 child rows still reference 7. Deleting the parent would leave them dangling (pointing at a row that no longer exists) → the DB blocks the delete with a foreign-key error. Student 7 and all 3 enrolments remain. Why this step? Referential integrity forbids a non-NULL foreign key that points at a missing row, so the safest default is to refuse the delete entirely.
ON DELETE CASCADE. The delete succeeds, and the 3 enrolment rows are automatically deleted too. Student count for 7 = 0, enrolment rows for 7 = 0. Why this step? Cascade repairs integrity by removing the children along with the parent, so no orphan can survive.
ON DELETE SET NULL. The delete succeeds; the 3 enrolment rows keep existing but their roll_no is set to NULL. Enrolment rows for 7 = 0, but 3 rows now have roll_no = NULL. Why this step? SET NULL preserves the history rows while dropping the now-invalid link — legal only because an FK column is allowed to be NULL (Ex 6).
Conclusion. Same DELETE, three outcomes: RESTRICT = blocked (nothing changes); CASCADE = 4 rows gone (1 student + 3 enrolments); SET NULL = student gone, 3 enrolments kept with NULL FK. All three end with no dangling non-NULL FK.

Verify: Count the surviving non-NULL FK values pointing at student 7 after each case: RESTRICT keeps 3 but the parent still exists (0 dangling); CASCADE leaves 0 rows (0 dangling); SET NULL leaves 0 non-NULL FKs (0 dangling). Integrity holds in every case. Aligns with Referential Integrity. ✅

Recall Quick self-test (click to reveal)

Single guaranteed-unique column — super key, candidate key, or both? ::: Both (unique ⇒ super; single column ⇒ minimal ⇒ candidate). Table R(A,B,C,D) with sole candidate key {A} — how many super keys? ::: $2^{3} = 8$ . Can a foreign key column contain duplicates? ::: Yes — only the referenced key must be unique. Can a column with some NULLs be a primary key? ::: No — primary keys must be NOT NULL; it can be a UNIQUE alternate key instead. ISBN changes on reprint and is sometimes missing — good primary key? ::: No; use a surrogate id as PK and keep isbn UNIQUE. If every column and every pair repeats, what is the candidate key? ::: The whole row (all columns together) — an all-key relation. What does ON DELETE CASCADE do to child rows? ::: Deletes them along with the referenced parent row.

Connections

Parent topic
Functional Dependencies — why {student_id, course_id} → grade names the candidate key
Referential Integrity — the delete/cascade behaviour in Ex 10
Relational Model — rows-as-a-set, the reason keys exist at all
Normalization — candidate keys drive the normal forms
Indexes — primary and unique keys are backed by indexes

Worked examples — Keys — primary, candidate, super, foreign, natural vs surrogate

The scenario matrix

Worked examples

Example 1 — the simplest possible key (cell C1)

Example 2 — the composite key (cell C2)

Example 3 — super keys that get chopped (cell C3)

Example 4 — the degenerate duplicate (cell C4)

Example 5 — NULLs in a would-be primary key (cell C5)

Example 6 — foreign key that repeats and goes NULL (cell C6)

Example 7 — when only the whole row is unique (cell C7)

Example 8 — natural vs surrogate, real-world word problem (cell C8)

Example 9 — exam twist: counting super keys (cell C9)

Example 10 — exam twist: foreign key on delete (cell C10)

Connections