Candidate Key in DBMS
Candidate key in DBMS: The candidate key is a single column or the set of columns that uniquely identifies the rows of data in the database table. It is a super key but with no repeated columns or attributes. So, this key is also called as minimal super key. Every table in the database must have at least a single candidate key.
Properties of Candidate key
Following are some of the properties of the candidate key:
- A candidate may have more than one column or attribute.
- The column, which is defined as the candidate key, will not contain any NULL value.
- It uniquely identifies each row in the table of the database.
- The values defined in the candidate key must be unique.
- Those attributes or columns which visible in some candidate key are called as the prime attributes.
We are describing the candidate key using the following example, so you can easily understand it.
This example uses one table Employee, which contains three columns or fields or attributes. The name of these three columns is Employee_ID, Employee_Name, Employee_EmailID.
| Employee_ID | Employee_Name | Employee_EmailID |
| 201 | Anuj | abc@gmail.com |
| 202 | Chetan | xyz@gmail.com |
| 203 | Lalit | mn@gmail.com |
| 204 | Lakshman | pqr@gmail.com |
| 205 | Lalit | def@gmail.com |
Now, we have to identify all the super key present in the table. After then, we have to eliminate the super keys, which contain the columns with duplicate attributes.
All the super keys of the Employee table are as follows:
1. { Employee_ID}: This column will contain all the unique values; hence this column can be used as a candidate key.
2. {Employee_EmailID}: As no two employees have the same emailID, hence Employee_EmailID column can be used as a candidate key.
3. { Employee_ID, Employee_EmailID }: As both ID and EmailID is unique for all the employees, this column combination can be a candidate key.
4. { Employee_ID, Employee_Name }: This column combination cannot be used as a candidate key because the two employees may have the same name.
5. { Employee_ID, Employee_Name, Employee_EmailID }: This column combination cannot be used as a candidate key because the Employee_name column may have duplicate values.
6. { Employee_Name, Employee_EmailID }: This column combination cannot be used as a candidate key because the Employee_name column may have duplicate values.
So, after elimination, following are the columns which can be the candidate key of the employee table:
{ Employee_ID},
{Employee_EmailID}, and
{ Employee_ID, Employee_EmailID }