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:

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.