Definition of Candidate Key in DBMS: A super key with no redundant attribute is known as candidate key. Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is that the candidate key should not have any redundant attributes. That’s the reason they are also termed as minimal super key.
Candidate Key Example
Lets take an example of table “Employee”. This table has three attributes: Emp_Id, Emp_Number & Emp_Name. Here Emp_Id & Emp_Number will be having unique values and Emp_Name can have duplicate values as more than one employees can have same name.
Emp_Id Emp_Number Emp_Name ------ ---------- -------- E01 2264 Steve E22 2278 Ajeet E23 2288 Chaitanya E45 2290 Robert
How many super keys the above table can have?
1. {Emp_Id}
2. {Emp_Number}
3. {Emp_Id, Emp_Number}
4. {Emp_Id, Emp_Name}
5. {Emp_Id, Emp_Number, Emp_Name}
6. {Emp_Number, Emp_Name}
Lets select the candidate keys from the above set of super keys.
1. {Emp_Id} – No redundant attributes
2. {Emp_Number} – No redundant attributes
3. {Emp_Id, Emp_Number} – Redundant attribute. Either of those attributes can be a minimal super key as both of these columns have unique values.
4. {Emp_Id, Emp_Name} – Redundant attribute Emp_Name.
5. {Emp_Id, Emp_Number, Emp_Name} – Redundant attributes. Emp_Id or Emp_Number alone are sufficient enough to uniquely identify a row of Employee table.
6. {Emp_Number, Emp_Name} – Redundant attribute Emp_Name.
The candidate keys we have selected are:
{Emp_Id}
{Emp_Number}
Note: A primary key is selected from the set of candidate keys. That means we can either have Emp_Id or Emp_Number as primary key. The decision is made by DBA (Database administrator)
kamal pratap says
Sir, Can a Candidate key contain NULL values ? If yes, then how many?
Dibbyendu says
A primary key is being selected from the group of candidate keys. That means we can either have Emp_Id or Emp_Number as primary key. Now, a primary key can’t have a null value [we learnt it in Primary key ]. Hence candidate key must not contain a null value…
Amit Sharma says
No Candidate key does not contain NULL values as a Primary key is selected by the group of Candidate key and as we know that Primary Key has unique constraint and NOT NULL.
Thanks!