Detailed explanation
A super key is a set of one or more columns that uniquely identifies each row in a relation. A candidate key is a minimal super key: it guarantees uniqueness, and removing any column from it would destroy that guarantee. Here, minimal means that no proper subset is also a super key; it does not mean having the fewest columns among all possible keys. Every candidate key is therefore a super key, but not every super key is a candidate key.
Consider employees(emp_id, email, name), assuming both emp_id and email are required and independently unique. The sets {emp_id} and {email} are candidate keys because each identifies an employee without needing another column. The sets {emp_id, name}, {email, name}, and {emp_id, email} are also super keys, but none is minimal because at least one column can be removed while preserving uniqueness. The name column alone is not a key unless the business rules guarantee unique names. Key status depends on rules that hold for every valid database state, not just on whether today's data happens to contain duplicates.
One candidate key is selected as the primary key; the remaining candidate keys are called alternate keys. A candidate key can also be composite. For example, in enrolments(student_id, course_id), the pair may identify an enrolment even though neither column identifies it individually. To enforce candidate-key behaviour in SQL, use PRIMARY KEY for the chosen key and generally UNIQUE together with NOT NULL for alternate keys. UNIQUE alone is not a portable substitute because NULL handling differs across database systems. Databases commonly enforce uniqueness using unique indexes, although implementation details vary.
This distinction matters when designing constraints, identifying rows reliably, and reasoning about functional dependencies and normalisation. Interviewers usually check whether you can explain both uniqueness and minimality, justify the assumptions in an example, and recognise composite keys. A strong answer also separates logical properties from implementation choices: adding extra columns preserves a super key's uniqueness, but it does not create another candidate key.
Key points to remember
- A super key uniquely identifies each row and may contain unnecessary columns.
- A candidate key is a super key with no proper subset that is also a super key.
- Minimal does not mean the smallest column count across all candidate keys.
- One candidate key becomes the primary key; the others are alternate keys.
- Keys follow schema and business rules, not accidental uniqueness in current data.
Common mistakes candidates make
- Calling {emp_id, name} a candidate key when emp_id alone guarantees uniqueness.
- Assuming email is a candidate key without establishing uniqueness and non-nullability.
- Treating UNIQUE alone as equivalent to a candidate key without considering NULL behaviour.
Likely follow-up questions
Can candidate keys in the same table have different numbers of columns?
Yes. A table could have candidate keys {employee_id} and {country_code, national_id}, provided each is unique and no column can be removed from the composite key. Minimality is tested within each key, not by comparing their sizes.
How do you enforce an alternate candidate key in SQL?
Generally, declare its columns NOT NULL and add a UNIQUE constraint over the complete key. For a composite key, the combination must be unique; individual columns need not be unique.
How do you choose the primary key from candidate keys?
Prefer a key that is stable, compact, and practical for references from other tables. For employees, emp_id is often preferable to email because email addresses may change, while email can remain an enforced alternate key.
Interview tip: Define uniqueness and minimality first, then use the employees example to show exactly why an extra column makes a super key non-minimal.