Home »
MySQL
MySQL CANDIDATE Key
MySQL | CANDIDATE Key: Learn about the MySQL CANDIDATE Key, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 15, 2022
CANDIDATE Key
In the simplest language, a candidate key is a key that is a subset of the super key. As we know, a super key is the group of attributes/columns which distinctly identify the records, whereas a candidate key is the minimal set of super keys.
As the super key is a theoretical concept in the same way the candidate key is more of a theoretical concept, but a very crucial concept.
Let us understand about candidate key via example;
Suppose we have a table named student details and inside this table, we have the following columns,
- Student id
- Student name
- Roll number
- DOB
- Email
- Address
- Branch
According to the above table, we can have the following super keys,
- Student id + student name: This combination will always give unique records.
- Student_id + student name + student roll number: This combination will be also unique so the set of these attributes will be considered as super key.
- Student_id + student name + student roll number + email: This combination will be also unique so the set of these attributes will be considered as super key.
We can make many super keys in one table, the only condition it should follow is that, it should be unique, doesn't matter if it is a single attribute or group of attributes, but it should give distinct records.
But if we talk about candidate key, then a minimal set of super key which can distinctly identify the records is called candidate key, which signifies that all candidate keys can also be super keys, but all super keys cannot be called candidate keys.
Here student id, roll number, and email attributes can easily identify the record uniquely
Since the super key is the superset, in a table the number of super keys will be more than the number of candidate keys.
The only similarity between super key and candidate key is that they both can have NULL as their values; there is no such restriction on this constraint.