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.
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.
is candidate key also primary key?
There 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.
why not the emp_name is the part of candidate key .though it is also having unique set of values???
Because more than one employees can have same name.
candidate keys are always come with pair so, why{Emp_SSN, Emp_Number} is not a candidate key
No, candidate keys are not necessarily to be a pair of attributes.