Keys — primary, candidate, super, foreign, natural vs surrogate: Question bank

Intuition How to use this bank — and one picture to hold in your head

Every item below is a trap: a statement that sounds right until you poke it, or a boundary case the definitions quietly cover. Read the question, cover the answer, and say your reasoning out loud before revealing. If your reason differs from the answer's reason — even when the verdict matches — that's the gap to close.

Before the traps, look at the figure below. It is the whole topic as nested boxes: every primary key lives inside "candidate keys", every candidate key lives inside "super keys", and foreign keys are arrows that reach out of one table to borrow another's identity. Keep this shrinking-box picture in mind — most traps are just "which box does this belong in?".

The vocabulary here (super key, candidate key, primary key, foreign key, natural vs surrogate) is all built from scratch in the parent note: the topic note. Everything is one idea in disguise — "what uniquely picks out exactly one row, and how do tables borrow that identity?"

True or false — justify

Every candidate key is a super key.

True. A candidate key is a super key that happens to be minimal, so it still guarantees uniqueness — it simply carries no redundant column.

Every super key is a candidate key.

False. A super key may include useless extra columns (e.g. the set

{r o l l_n o, nam e}

— two columns tested together); a candidate key must be minimal, so this super key is disqualified because dropping name still leaves a unique key.

A table can have more than one candidate key.

True. If both

{r o l l_n o}

and

{e mai l}

independently identify a row, both are candidate keys; the designer then picks one as primary and the rest become alternate keys.

A table can have more than one primary key.

False. "Primary" means the one chosen handle; by definition a table has at most one primary key, though it may sit on several columns (a composite key still counts as one key).

A primary key may contain a NULL value.

False. NULL means "unknown", and an unknown value cannot identify a row, so the primary key is NOT NULL by definition — this is exactly why it's stronger than a plain UNIQUE constraint.

A UNIQUE column may contain a NULL.

True (in most SQL engines). UNIQUE only forbids duplicate known values; it permits NULLs (often several), which is precisely the gap between UNIQUE and PRIMARY KEY.

Foreign key column values must be unique.

False. One student can appear in many enrolment rows, so the FK repeats freely — it is the referenced candidate/primary key that must be unique, not the FK column.

A foreign key can be NULL.

True (unless explicitly forbidden). A NULL FK means "this row references nothing yet" (e.g. an order with no assigned courier). The deeper reason: SQL's default MATCH SIMPLE rule skips the integrity check whenever any FK column is NULL, so an unset reference is treated as "not pointing anywhere" rather than "pointing at a missing row" — hence it's allowed.

A surrogate key is always better than a natural key.

False. Surrogates never change, but without a UNIQUE constraint on the natural data two rows with different ids but the same real-world email slip in as duplicate entities.

A composite key (two columns together) can still be a candidate key.

True, provided the pair is unique and neither column alone is unique — then it's minimal and qualifies (e.g.

{s t u d e n t_i d, co u r se_i d}

in an enrolment table).

Adding an extra column to a super key can break its uniqueness.

False. Any superset of a super key is still a super key; extra columns can only add detail, never create duplicates where there were none.

{A}

is a candidate key, then

{A, B}

is also a candidate key.

False.

{A, B}

is a super key but not minimal (drop

B

and

{A}

still works), so it fails the candidate test.

Spot the error

"I made name the primary key because names are usually unique in my class."

Error: "usually" is not "always" — two students can share a name, so it's not even a super key; a key must guarantee uniqueness with certainty, not by luck.

"Foreign keys guarantee the referenced row can never be deleted."

Error: the DB protects the reference, but you can still delete via ON DELETE CASCADE (removes the children too) or SET NULL; integrity is preserved, not deletion prevented.

"I'll use email as the primary key since it's unique and meaningful."

Risky: email is a natural key that changes when a user updates it. Downstream that's expensive — every foreign key that copied the old email must be rewritten (a wide ON UPDATE CASCADE touching many rows), and because emails are long variable-length strings, indexes on them fragment and bloat compared with a compact integer. A meaningless surrogate id avoids all of this.

{r o l l_n o, e mai l}

is a candidate key because both parts are unique."

Error: since

{r o l l_n o}

alone is already unique, the pair is not minimal — it's merely a super key, and in fact contains two separate candidate keys.

"A table with no primary key is impossible."

Error: a table can physically exist without a declared primary key; it's just bad design — rows become impossible to reference reliably and duplicates can creep in.

"Since the FK must match the parent's key, both columns need identical names."

Error: matching is by value and type, not name. Enrolment.student_ref can reference Student.roll_no — names are for humans, not the constraint.

"Making a column UNIQUE turns it into the primary key automatically."

Error: UNIQUE only enforces no-duplicates and still allows NULL; it does not make the column the official identifier nor forbid NULLs.

Why questions

Why must a candidate key be minimal rather than just unique?

So we get the smallest honest identifiers — minimality strips redundant columns, giving clean choices for the primary key and avoiding bloated indexes and foreign keys.

Why does a primary key need to be stable (rarely change)?

Because foreign keys elsewhere copy its value; if it changes, every referencing row must change in lockstep, which is costly and error-prone — a surrogate id sidesteps this by being meaningless and fixed.

Why can a foreign key value repeat but a primary key value cannot?

The primary key identifies rows in its own table (must be unique), while the foreign key merely points to one of those rows — many rows can point to the same target (many enrolments → one student).

Why do surrogate keys stay popular despite losing human readability?

They are stable, compact, always present, and carry no business meaning that could ever go stale — the readability loss is paid back by never having to update the key.

Why must the value referenced by a foreign key be a candidate (or primary) key, not just any column?

A FK must point to exactly one row; only a unique column guarantees the reference is unambiguous — pointing at a non-unique column could match many rows at once.

Why is "every super key is unique but not every unique-looking set is a super key" a subtle trap?

Uniqueness must hold across all possible rows, not just the rows currently present — a column can look unique in today's data yet allow future duplicates, so it was never truly a super key.

Why keep a UNIQUE constraint on the natural key even after adding a surrogate primary key?

The surrogate only stops duplicate ids, not duplicate real entities — without a UNIQUE natural constraint, two rows with the same email and different ids both survive.

Edge cases

If a table has exactly one column and that column is unique, how many candidate keys does it have?

One — the single column is minimal and unique, so it is the only candidate (and, if chosen, the primary) key.

Can the entire set of all columns be a super key?

Yes, if every row is distinct then all-columns-together is unique — it's a valid (usually non-minimal) super key, which is why a set-of-rows always has at least one super key.

What if two rows are completely identical in every column?

Then no super key exists — this is why the relational model forbids duplicate rows; a proper table is a set, so identical rows are collapsed to one (see the figure above).

Can a foreign key reference the same table it lives in?

Yes — a self-reference, e.g. Employee.manager_id referencing Employee.emp_id; the rules are identical, the parent and child just happen to be one table.

Can a foreign key reference an alternate (non-primary) candidate key?

Yes, as long as that referenced column is guaranteed unique (declared as a candidate/UNIQUE key); pointing to a primary key is common but not required.

Is a NULL allowed in part of a composite foreign key?

In standard SQL, if any column of a composite FK is NULL, the reference check is typically skipped (MATCH SIMPLE) — a subtle case that can silently let orphan-looking rows exist.

Can a single column be both part of the primary key and a foreign key at once?

Yes — in a link table, e.g.

{s t u d e n t_i d, co u r se_i d}

is the composite primary key while each column is also a foreign key to its parent table.

Recall Comparative visual summary — the five key types at a glance (click to reveal)

The matrix below (figure) lines up all five key types against the properties that separate them: unique?, minimal?, NOT NULL?, may repeat?, meaningful?. Reading a column tells you what a key type must satisfy; reading a row tells you which key types share that property.

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

Super key → unique, extras allowed (not minimal).
Candidate key → unique AND minimal.
Primary key → the one candidate chosen; unique + NOT NULL.
Foreign key → may repeat, may be NULL; points at a unique key elsewhere.
Natural = meaningful-but-changeable; Surrogate = meaningless-but-stable. If any of those five felt shaky, re-read the matching trap above.

Connections

Parent topic note
Relational Model — why a table is a set with no duplicate rows
Functional Dependencies — the formal engine behind "unique determines"
Referential Integrity — the rule foreign keys enforce
Normalization — keys drive the decomposition into normal forms
Indexes — why compact stable keys index well
Entity-Relationship Model — where natural vs surrogate choices first appear