What is the difference between a super key, a candidate key and a primary key?
A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key with no removable column. The primary key is the one candidate key you choose to identify rows.
Explain it simply
Think of identifying a student. Roll number alone works. Roll number plus name also works but the name is extra baggage. Email alone works too. All of those are super keys. The minimal ones, roll number and email, are candidate keys. The college picks roll number as the official one: that is the primary key.
Example
Table students(roll_no, email, name, city) where roll_no and email are each unique.
| Key set | Super key? | Candidate key? |
|---|---|---|
| {roll_no} | Yes | Yes |
| {email} | Yes | Yes |
| {roll_no, name} | Yes | No, name is removable |
| {name} | No, names can repeat | No |
- Super keys here are every set containing roll_no or email.
- Candidate keys are {roll_no} and {email}.
- Primary key: choose {roll_no}.
- The unchosen candidate {email} is called an alternate key, and is usually declared UNIQUE.
Every candidate key is a super key, but not the other way around.
What to say in the interview
"A super key uniquely identifies rows, a candidate key is a minimal super key, and the primary key is the chosen candidate. Remaining candidates are alternate keys, enforced with UNIQUE."
Common mistakes
- Calling {roll_no, name} a candidate key, when it is only a super key.