Keys: Primary, Foreign, Candidate and Super Keys

Database keys are a DBMS interview staple — "difference between primary key and candidate key" and "what is a foreign key?" are near-guaranteed. Keys underpin integrity and normalization, so getting the hierarchy right is essential.

The key hierarchy

• Super key — any set of attributes that uniquely identifies a row (may have extra, redundant attributes).
• Candidate key — a minimal super key: no proper subset can still uniquely identify a row. A table can have several.
• Primary key — the chosen candidate key. Exactly one per table; unique + NOT NULL.
• Alternate key — a candidate key not selected as the primary key.
• Foreign key — an attribute that references the primary key of another table, enforcing referential integrity; it can be NULL and can repeat.
• Composite key — a key made of multiple columns.

Key	Unique	Minimal	NULL allowed
Super	yes	no	—
Candidate	yes	yes	no
Primary	yes	yes	no
Foreign	no	—	yes

Exam Tricks & Tips

• 🎯 Every candidate key is a super key, but not every super key is a candidate key (candidate = minimal) — the classic subset relationship.
• 🎯 A primary key is unique and NOT NULL; a foreign key can be NULL and can have duplicates.
• 🎯 A table has one primary key but can have many candidate/alternate and foreign keys.
• 🎯 A foreign key enforces referential integrity — you can't reference a value that doesn't exist in the parent table.
• 🎯 A composite key uses multiple columns together; no single column alone is unique.
• 🎯 Removing any attribute from a candidate key breaks uniqueness — that minimality test defines it.
• ❌ Common mistake: thinking a primary key can be NULL — it cannot; only foreign keys (and other columns) may be NULL.

Expected pattern

"Super key vs candidate key vs primary key", "can a foreign key be NULL?", "how many primary keys per table?", "what is referential integrity?", and identifying candidate keys from functional dependencies.

Quick recap

Super key (unique, maybe redundant) ⊃ candidate key (minimal unique) → primary key (one chosen, unique + NOT NULL); alternate = unused candidate. Foreign key references another table's primary key (can be NULL, enforces referential integrity). Composite key spans multiple columns.

Cover the answer, recall, then check. 12 cards on database keys.

Q1. What is a super key?
A1. Any set of attributes that uniquely identifies a row, possibly including redundant extra attributes.

Q2. What is a candidate key?
A2. A minimal super key — no proper subset of it can still uniquely identify a row.

Q3. Relationship between super keys and candidate keys?
A3. Every candidate key is a super key, but not every super key is a candidate key (candidate keys are minimal).

Q4. What is a primary key?
A4. The chosen candidate key for a table; it must be unique and NOT NULL, and there is exactly one per table.

Q5. Can a primary key be NULL?
A5. No — a primary key must be unique and NOT NULL.

Q6. What is an alternate key?
A6. A candidate key that was not selected as the primary key.

Q7. What is a foreign key?
A7. An attribute that references the primary key of another table, enforcing referential integrity.

Q8. Can a foreign key be NULL or have duplicates?
A8. Yes — a foreign key may be NULL and may repeat, unlike a primary key.

Q9. What is referential integrity?
A9. The rule that a foreign key value must match an existing primary key value in the referenced table (or be NULL).

Q10. What is a composite key?
A10. A key composed of two or more columns that together uniquely identify a row, where no single column is unique.

Q11. How many primary keys can a table have?
A11. Exactly one — though it may be composite (span multiple columns).

Q12. How do you test whether a key is a candidate key?
A12. It must be unique and minimal — removing any attribute must break its uniqueness.

Keys are how the relational model guarantees that every row is findable and that relationships between tables stay consistent. The family of key terms — super, candidate, primary, foreign — is a favourite exam area because the definitions nest precisely.

Core idea: a key is a set of attributes that identifies rows. A super key uniquely identifies a row (possibly with extra attributes); a candidate key is a minimal super key; the primary key is the chosen candidate key; a foreign key references another table's primary key to link tables.

How it works

Beginner — uniqueness and identification

In a table Student(roll_no, email, name, dept), both roll_no and email uniquely identify a student. Any attribute set that guarantees uniqueness can serve as an identifier — that's the starting point.

Intermediate — the nesting of key types

• Super key: any attribute set that uniquely identifies a row. {roll_no}, {roll_no, name}, {email, dept} are all super keys (the extras are redundant but don't break uniqueness).
• Candidate key: a super key with no redundant attribute — remove any attribute and it stops being unique. {roll_no} and {email} are candidate keys; {roll_no, name} is not (name is superfluous).
• Primary key: the one candidate key the designer selects to be the row's official identifier. It is NOT NULL and unique.
• Alternate keys: the candidate keys not chosen as primary.

Relationship: every candidate key is a super key; every primary key is a candidate key. (Candidate ⊆ Super.)

Advanced — foreign keys and referential integrity

A foreign key is an attribute in one table that references the primary key of another (or the same) table, enforcing referential integrity: you cannot insert a value that doesn't exist in the referenced table.

CREATE TABLE Enrolment (
    roll_no INT REFERENCES Student(roll_no),  -- foreign key
    course  VARCHAR(10),
    PRIMARY KEY (roll_no, course)             -- composite primary key
);

A FK may be NULL (unlike a primary key) — meaning "no relationship yet". Delete/update behaviour (ON DELETE CASCADE, SET NULL, RESTRICT) governs what happens to child rows when a parent is removed. A composite key is any key made of multiple attributes.

Worked example — identifying keys

Table Employee(emp_id, ssn, email, name) where emp_id, ssn and email are each unique.

• Candidate keys: {emp_id}, {ssn}, {email} (each minimal and unique).
• Super keys: all candidate keys plus any superset, e.g. {emp_id, name}, {ssn, email} — infinitely many.
• Primary key: pick one, say emp_id (stable, system-generated).
• Alternate keys: ssn and email.
  If Payroll has emp_id REFERENCES Employee(emp_id), that's a foreign key and you can't add a payroll row for a non-existent employee.

Interview & exam relevance

Guaranteed: "difference between super key, candidate key and primary key", "can a foreign key be NULL?" (yes; a primary key cannot), "what is referential integrity", and counting candidate/super keys given functional dependencies. GATE frequently asks "how many super keys" given the candidate keys.

Tricks & gotchas

• Every candidate key is a super key, but not vice versa — candidate = minimal super key.
• A primary key is NOT NULL + unique; a foreign key can be NULL and can have duplicates.
• If a candidate key has k attributes in an n-attribute table, the number of super keys containing it is 2^(n−k) (each other attribute is in or out).
• Mnemonic: "Super = unique; Candidate = minimal unique; Primary = the chosen one; Foreign = points elsewhere."

Worked Example

Problem: Given the relation Student(roll_no, email, aadhaar, name, dept), where roll_no, email, and aadhaar are each unique and non-null, identify all candidate keys, give two super keys, choose a primary key with justification, and add a foreign key to Department(dept_id, ...).

Solution:
Definitions: a super key is any set of attributes that uniquely identifies a row; a candidate key is a MINIMAL super key (no attribute can be removed while keeping uniqueness); the primary key is one candidate key chosen by the designer; a foreign key references a key in another table.

Candidate keys: each of roll_no, email, aadhaar uniquely identifies a student and is minimal on its own (single attribute, cannot be reduced). So there are three candidate keys: {roll_no}, {email}, {aadhaar}. name and dept are not unique (many students share a dept), so they are not keys.

Super keys: any superset of a candidate key is a super key. Examples: {roll_no} (also a candidate key), {roll_no, name}, {email, dept}, {aadhaar, name, dept}. There are many super keys but only three candidate keys — every candidate key is a super key, but not every super key is minimal (candidate).

Primary key choice: pick the candidate key that is stable, compact, and non-sensitive. roll_no is the best choice: it never changes for a student, is short, and is not personally sensitive. email can change; aadhaar is sensitive PII that should not be a widely-referenced identifier. So PRIMARY KEY (roll_no). The other candidate keys become UNIQUE constraints (alternate keys).

Foreign key: dept references Department. Add FOREIGN KEY (dept) REFERENCES Department(dept_id). This enforces referential integrity — every dept value in Student must exist in Department, so you cannot enrol a student in a non-existent department, and you cannot delete a referenced department without handling the dependents (e.g. ON DELETE RESTRICT/CASCADE).

CREATE TABLE Student (
  roll_no  INT PRIMARY KEY,
  email    VARCHAR(100) UNIQUE NOT NULL,   -- alternate key
  aadhaar  CHAR(12)     UNIQUE NOT NULL,    -- alternate key
  name     VARCHAR(80)  NOT NULL,
  dept     INT,
  FOREIGN KEY (dept) REFERENCES Department(dept_id)
);

Answer: Candidate keys = {roll_no}, {email}, {aadhaar}; example super keys = {roll_no, name}, {email, dept}. Choose roll_no as primary (stable, compact, non-sensitive) and mark the others UNIQUE. dept is a foreign key to Department(dept_id), enforcing referential integrity.