Candidate Key

Definition

A candidate key is a minimal super key — a set of attributes that uniquely identifies every row, where removing even one attribute from the set would destroy that uniqueness. A relation can have more than one candidate key; each one is a genuine "candidate" to become the table's primary key.

Running example used throughout this chapter — a students(id, name, email, department_id) table:

How It Works

For students(id, name, email, department_id), assuming both id and email are independently unique:

• {id} — a candidate key (minimal, unique).
• {email} — also a candidate key (minimal, unique) — a completely separate one from {id}.
• {id, name} — NOT a candidate key: it's a super key, but not minimal, since {id} alone already suffices.

When a table has more than one candidate key (here, {id} and {email}), the database designer picks ONE to be the actual primary key (5.3); the others remain candidate keys that didn't get chosen — these are sometimes specifically called alternate keys (5.4).

Edge Cases and Pitfalls

• Minimality is checked attribute-by-attribute: {student_id, course_id} is minimal for an enrollments table only if neither {student_id} alone nor {course_id} alone is unique on its own — if either sub-part IS already unique by itself, the pair isn't minimal.
• A table can have zero natural candidate keys if the real-world data has no attribute or combination that's guaranteed unique — this is exactly the situation that motivates introducing a surrogate key (5.8) like an auto-incrementing id.
• Multiple candidate keys existing side by side is completely normal and often desirable (e.g. both an internal id and a business email/SSN/ISBN can each independently identify a row) — it's not a design smell.

Key Takeaways

• Candidate key = minimal super key; a table can have several.
• Exactly one candidate key is chosen as the primary key; the rest are alternate keys.
• Minimality must be verified for every attribute in the set, not assumed.