Definition: A super key is a set or one of more columns (attributes) to uniquely identify rows in a table. Often people get confused between super key and candidate key, so we will also discuss a little about candidate key here.
How candidate key is different from super key?
Answer is simple – Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is: It should not have any redundant attribute. That’s the reason they are also termed as minimal super key.
Let’s take an example to understand this: Employee table
| Emp_SSN | Emp_Number | Emp_Name |
| 123456789 | 226 | Steve |
| 999999321 | 227 | Ajeet |
| 888997212 | 228 | Chaitanya |
| 777778888 | 229 | Robert |
Super keys:
- {Emp_SSN}
- {Emp_Number}
- {Emp_SSN, Emp_Number}
- {Emp_SSN, Emp_Name}
- {Emp_SSN, Emp_Number, Emp_Name}
- {Emp_Number, Emp_Name}
All of the above sets are able to uniquely identify rows of the employee table.
Candidate Keys:
As I stated above, they are the minimal super keys with no redundant attributes.
- {Emp_SSN}
- {Emp_Number}
Only these two sets are candidate keys as all other sets are having redundant attributes that are not necessary for unique identification.
Super key vs Candidate Key
I have been getting lot of comments regarding the confusion between super key and candidate key. Let me give you a clear explanation.
1. First you have to understand that all the candidate keys are super keys. This is because the candidate keys are chosen out of the super keys.
2. How we choose candidate keys from the set of super keys? We look for those keys from which we cannot remove any fields. In the above example, we have not chosen {Emp_SSN, Emp_Name} as candidate key because {Emp_SSN} alone can identify a unique row in the table and Emp_Name is redundant.
Primary key:
Primary key is being selected from the sets of candidate keys by database designer. So Either {Emp_SSN} or {Emp_Number} can be the primary key.
Why Emp_Name is not candidate or super key ?
Emp_Name cannot be both because names are not unique e.g. there could be hundreds of Jack inside the database.
sir why don’t Emp_Number is a super key
Emp_Number is a super key, in fact with just Emp_Number we can lead up to Emp_Name or Emp_SSN;
Emp_Name is NOT a super key because we can have 2 “Steve” or 3 or 4 in that table.. Name are not unique, we cannot say the same for SSN codes or Emp_Number. Bye
Gabriele
It is a super key as mentioned in the article.
What is the difference between super key and candidate key?
I have added more details on this in the guide, Please refer the added section above.
I have doubt that
{Emp_SSN, Emp_Number} pair also Candidate Keys?.because both are not a redundant attributes
No {Emp_SSN, Emp_Number} pair is not a candidate key because {Emp_SSN} alone is sufficient to identify a unique row in table. The same applies for {Emp_Number}. So based on this we can say that {Emp_SSN} and {Emp_Number} both are candidate keys but {Emp_SSN, Emp_Number} is not a candidate key.