Difference between Primary Key and Candidate Key
Keys in MySQL are an attribute or a set of attributes used to access records from tables. They are also used to construct a relationship between two tables. Both Primary and Candidate Key identifies each record uniquely in a table or relation. The most popular difference among them is that a table can have only one primary key but can have more than one candidate key. In this article, we are going to compare essential differences between Primary and Candidate Keys based on various parameters. Before making a comparison, we will discuss in brief about these keys.
What is Primary Key?
The primary key is a unique or not-null key that uniquely identifies each record in a table or relation. It is a minimal super key; that’s why it can contain only one primary key in any relationship. For example, suppose we have a table named students with ID, Name, Age, and Address columns. Here we can make only the ID column a primary key because all other column values can be the same, but the ID column can’t be the same.
What is Candidate Key?
A candidate key is an attribute or a set of attributes that identify each record in a table or relation uniquely but noted that a table could contain multiple candidate keys. This key can store a NULL value that opposes in a primary key. For example, suppose we have a table named students with ID, Name, DOB, Age, and Address columns. Here we can figure out two candidate keys that are {ID} and {Name, DOB}. Hence, it clarifies that more than one candidate key is available to identify the table or relation uniquely.
Since a table or relation can have more than one candidate key, one candidate key can qualify to become a primary key out of all candidate keys. Note that each candidate key can be a primary key, but only one should be chosen as the primary key. The rule to become a primary key among candidate keys is that the key’s attribute values must be unique and can never be Null for any domain.
Key differences between Primary and Candidate Key
The following points explain the main differences between primary and candidate keys:
- The primary key is a unique and essential attribute of a table or relation. In contrast, the candidate keys have many candidates, among which one candidate key can be selected as a primary key.
- The fundamental difference among both keys is that a table or relation in a schema can have only one primary key, but there can be multiple candidate keys.
- It is not mandatory to define a primary key, but there cannot be a relationship without the candidate key.
- The primary key attribute can never be a NULL because its main function is to identify a record in a table or relation uniquely. We can also use the primary key as a foreign key in other relations. Therefore, it cannot be NULL. This feature helps in finding the records in a referenced relation. The candidate key can be NULL unless the attribute constraint is specified as not-null.
- Each primary key can be a candidate key, but vice-versa is not possible.
Primary Key vs. Candidate Key Comparison Chart
The following comparison chart explains their main differences in a quick manner:
| Comparison Basis | Primary Key | Candidate Key |
|---|---|---|
| Definition | It is a unique and non-null key to identify each table’s records in a schema uniquely. | It is also a unique key to identify records in relation or table uniquely. |
| Basic | A table or relation can contain only one primary key. | A table or relation can have more than one candidate key. |
| NULL | Any column of a primary key cannot be NULL. | The column of a candidate can contain a NULL value. |
| Objective | It is the essential part of a table or relation. | It signifies which key can be used as a primary key. |
| Use | It can be used as a candidate key. | It may or may not be used as a primary key. |
| Specify | It is not mandatory to specify a primary key for any relation. | There cannot be a relationship without specifying the candidate key. |
| Example | Consider a table “student” with columns (roll_no., name, class, DOB, email, mobile). Here roll_no column can be a primary key for the relationship because it identifies the student’s records uniquely. | The roll_no, mobile, and email columns can be candidate keys in the given table because they can uniquely identify student’s records. |
Conclusion
In this article, we have made a comparison between primary and candidate key. Here we conclude that it is optional to specify a primary key for a relation. But if we are declaring a relationship, candidate keys must be present in that relation.