Candidate Key in SQL | What is Candidate Key in RDBMS?

Summary: A Candidate Key in SQL is a specific type of field in a relational database that can identify each unique record independently of any other data. Candidate Key in SQL plays an important role, Let’s understand with an example in detail.

What is Candidate Key in RDBMS

A candidate key defined as it is a single key or a group of multiple keys that uniquely identify rows in a table.
This key is a subset of Super keys and is devoid of any unnecessary attributes, it is not important for uniquely identifying tuples.
The value for the CK is unique and non-null for all tuples. furthermore, every table has to have at least 1 (one) Candidate key. however, there can be more than one Candidate Key too.

In another word, we can say that this key is a subset of a super key set where the key which contains no redundant attribute is none aside from a Candidate Key.

Role of a Candidate Key in DBMS

This key plays an important role to identify a table row or column uniquely. Also, the value of a candidate key can’t be Null. The description of a candidate key is “no redundant attributes” and is a “minimal representation of a tuple,” as per the specialists.

Important Properties

• It must contain unique values
• This key in SQL may have multiple attributes
• Mustn’t contain null values
• It should contain minimum fields to confirm/ensure uniqueness
• This key is able to uniquely identify each record in a table

Example

The following given below table Emp_ID, Emp_Code, and Emp_Email are candidate keys that help us to uniquely identify the EMPLOYEE_DETAILS record in the table.

Understand the Working Concept of Super Key and Candidate key

Let’s consider another table Student_Details, we have the following BELOW attributes:

Stu_SN: The SSN number is stored in this field.
Stu_ID: An attribute that stores the value of the Students identification number.
Stu_Name: An attribute that stores the name of the Student holding the specified students’ id.
Stu_Email: An attribute that stores the student email id of the specified students.
The Student_Details table is given below that will help you understand better:

How to Obtained Super Keys?

So, from the above Student_Details table, we obtained the below given super keys

Set of Super Keys Obtained

Furthermore, from these sets of super keys as given above, we can conclude the CK.
Thus, in order to pick up the these keys, the method is to analyze and form the PK-primary keys as much as we can. So, here we just need to identify those sets from the super key sets that alone can identify the whole table, or we can say the other attributes of the table. Thus, the result is:

So, as you can see that these three attributes obtained can identify the other non-prime attributes of the table. Thus, all these are the candidate keys and from which we can pick the most appropriate attribute that can simply identify all records of the table, which will be described as the PK.

Difference Between Candidate Key and Super Key