A key can be referred to as an attribute/a set of attributes that help us identify a row (or tuple) uniquely in a table (or relation). A key is also used when we want to establish relationships between the different columns and tables of a relational database.
Candidate key
- A candidate key is an attribute or a set of attributes that uniquely identify a tuple.
- Candidate keys are defined as a distinct set of attributes from which the primary key can be selected.
- Candidate keys are allowed to have NULL values.
- A candidate key is a minimal super key with no redundant attributes.
Why Candidate Key is Called a Minimal Super Key?
A candidate key is called a minimal super key because we select a candidate key from a set of super keys such that the selected candidate key is the minimum attribute required to uniquely identify the table.
Pre-requisites: Types of Keys
Example-
Let’s take an employee table with attributes Dept_Id, Emp_Id, Name, Aadhar_No, and Email_Id.
| Dept_id | Emp_id | Name | Aadhar_No | Email_id |
|---|---|---|---|---|
| 1 | 1 | Satyanshu | 9999 | satya@gmail.com |
| 2 | 2 | Aditya | 8888 | aditya@gmail.com |
| 2 | 3 | Avneesh | 7777 | avneesh@gmail.com |
| 4 | 4 | Aryan | 6666 | aryan@gmail.com |
A super key is a collection of all possible attributes that can be used to uniquely identify the rows in a given relation.
A super key is a superset of a candidate key.
Super Keys for the above table
{Emp_Id}
{Emp_Id, Aadhar_No}
{Aadhar_No}
{Aadhar_No, Email_Id}
{Email_Id}
{Emp_Id, Email_Id}Candidate keys are as follows
{Emp_Id}
{Aadhar_No}
{Email_Id}