Definition of Super Key in DBMS: A super key is a set of one or more attributes (columns), which can uniquely identify a row in a table. Often DBMS beginners get confused between super key and candidate key, so we will also discuss candidate key and its relation with super key in this article.
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:
Table: Employee
Emp_SSN Emp_Number Emp_Name --------- ---------- -------- 123456789 226 Steve 999999321 227 Ajeet 888997212 228 Chaitanya 777778888 229 Robert
Super keys: The above table has following super keys. All of the following sets of super key are able to uniquely identify a row of the employee table.
- {Emp_SSN}
- {Emp_Number}
- {Emp_SSN, Emp_Number}
- {Emp_SSN, Emp_Name}
- {Emp_SSN, Emp_Number, Emp_Name}
- {Emp_Number, Emp_Name}
Candidate Keys: As I mentioned in the beginning, a candidate key is a minimal super key with no redundant attributes. The following two set of super keys are chosen from the above sets as there are no redundant attributes in these sets.
- {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:
A Primary key is selected from a set of candidate keys. This is done by database admin or database designer. We can say that either {Emp_SSN} or {Emp_Number} can be chosen as a primary key for the table Employee.
Ansh says
July 17, 2016 at 5:07 PMWhy Emp_Name is not candidate or super key ?
Lee says
May 14, 2017 at 11:50 AMEmp_Name cannot be both because names are not unique e.g. there could be hundreds of Jack inside the database.
anuj says
February 26, 2017 at 7:18 PMsir why don’t Emp_Number is a super key
Gabriele says
July 16, 2017 at 6:06 PMEmp_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
Chaitanya Singh says
April 6, 2018 at 4:43 AMIt is a super key as mentioned in the article.
Imran khan says
March 30, 2018 at 4:55 PMWhat is the difference between super key and candidate key?
Chaitanya Singh says
April 6, 2018 at 4:50 AMI have added more details on this in the guide, Please refer the added section above.
lavanya says
May 4, 2018 at 7:55 AMI have doubt that
{Emp_SSN, Emp_Number} pair also Candidate Keys?.because both are not a redundant attributes
Chaitanya Singh says
May 6, 2018 at 2:23 PMNo {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.
kirti says
September 17, 2018 at 1:29 PMis candidate key also primary key?
Chaitanya Singh says
December 11, 2018 at 5:02 AMThere can be number of candidate keys present in a table, however there is only one primary key. A primary key is always chosen from a set of candidate keys. The decision of choosing primary key from a set of candidate keys is made by database admin.
Sai kumar says
September 17, 2018 at 9:31 PMwhy not the emp_name is the part of candidate key .though it is also having unique set of values???
Chaitanya Singh says
December 11, 2018 at 4:58 AMBecause more than one employees can have same name.
keerthi says
October 17, 2018 at 1:25 PMcandidate keys are always come with pair so, why{Emp_SSN, Emp_Number} is not a candidate key
Chaitanya Singh says
December 11, 2018 at 5:00 AMNo, candidate keys are not necessarily to be a pair of attributes.