Difference Between Primary and Candidate Key

Keys are the attribute, or a set of attributes that are used to access tuples from a table or they are also used to construct a relationship between two tables. In this article, we are going to discuss Primary and Candidate Key and the differences between them.

Both Primary and Candidate Key uniquely identifies a tuple in a relation or table. But, the most important point that differentiates them is that there can be only one primary key in a relation. However, there can be more than one candidate key in a relation.

There are some more differences between Primary and Candidate Key which I will discuss with the help of comparison chart shown below.

Content: Primary Vs Candidate Key

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

Basis for Comparison	Primary Key	Candidate Key
Basic	There can be only one primary key in any relation.	There can be more than one candidate key in a relation.
NULL	No attribute of a Primary key can contain NULL value.	The attribute of a Candidate key can have NULL value.
Specify	It is optional to specify a primary key for any relation.	There can not be a relation without candidate key specified.
Feature	Primary key describes the most important attribute for the relation.	Candidate keys present candidates that can qualify for Primary Key.
Vice-Versa	A primary key is a candidate key.	But it not compulsory that each candidate key can be a primary key.

Definition of Primary Key

Primary Key is an attribute or a set of attributes that will uniquely identify each tuple in a relation. There can be only one primary key for each relation. It must be taken care that a primary key should never contain a NULL value, and it must have the unique value for each tuple in the relation. The values of the attribute/s of the primary key must be static, i.e., the value of the attribute should never or rarely changed.

One of the Candidate Keys gets qualified to become a primary key. The rules that a candidate key must qualify to become primary are that the key value should never be NULL and it must be unique for all tuples.

If a relation contains an attribute that is a primary key of some other relation, then that attribute is called foreign key.

It is advised to figure out the primary key of a relation before introducing other attributes of a relation as primary key identifies each tuple uniquely. It is better to choose a single attribute or a small number of attributes as a primary key it makes relation handling easy.

Now let us see an example of a Primary key.

Student{ID, First_name, Last_name, Age, Address}

Here we will first figure out candidate keys. I have figured out two candidate keys {ID} and {First_name, Last_name} as they will uniquely identify each student in the Student relation. Now, here I will choose ID as my primary key because sometimes it may happen that two students may have same first and last names, so it will be easy to trace a student with his ID.

Definition of Candidate Key

A candidate key is an attribute or a set of attribute that uniquely define a tuple in a relation. There an be more than one candidate key in a relation. These Candidate keys are the candidates that can qualify to become a primary key.

Though each candidate key qualifies to become a primary key, only one can be chosen as a primary key. The rules a candidate key requires to become primary key are the attribute value of the key can never be NULL in any domain of the key, it must be unique and static.

If all the candidate keys are qualifying for primary key, then an experienced DBA must take the decision to figure out the primary key. There can never be a relation without candidate key.

Let us understand the candidate key with an example. If we add some more attributes to Student relation, I discussed above.

Student{ID, First_name, Last_name, Age, Address, DOB, Department_name}

Here I can figure out two candidate keys that are {ID}, {First_name, Last_name, DOB}. So you can understand the candidate keys are one that uniquely identifies a tuple in a relation.

Conclusion

It is optional for a relation to specify a primary key. On the other hand, if you are declaring a relation, candidate keys must be present in that relation in order to construct a good relation.