Primary vs Candidate Key
In this post you will learn about the difference between Primary & Candidate key. Let’s have the definition first.
Primary Key: The key which identifies each record uniquely in a table is referred to Primary key. It must have unique (never before) values. The column which is primary key of a given table, can’t have NULL values. A table can have only one primary key.
Candidate Key: It contains one or more than one set of column(s) to uniquely identify a record of a table. If the concerned table contain more than one key, then out of all one will become primary key of table, and rest will become alternate keys. In short we can say that all primary keys are candidate keys.
One should be very careful while selecting key for a table. Wrong selection may result into bad effect on database architecture and normalization.
Difference between Primary key & Candidate Key
1. Primary key can’t contain Null value, but Candidate key value can have one NULL value.
2. If a column (part of candidate keys) contain one null value, then that column will remain as part of candidate key, but not be considered as primary key.
3. A table has only one column which can be represented as Primary Key, but on the other hand there can be more than one column which combines with each other and create Candidate Key (one of them would be considered as Primary key & rest would be Alternate Keys).
Example of Difference between Primary key & Candidate Key:
USE Northwind GO SELECT * FROM Customers GO SELECT DISTINCT CustomerID FROM Customers GO SELECT DISTINCT CompanyName FROM Customers GO SELECT DISTINCT Phone FROM Customers GO
In the above example, the result contains 91 rows.
So we can say that CustomerID, CompanyName and Phone together can become as possible Candidate key.
CustomerID alone can be considered as primary Key, because every customer will have an ID. ID can’t be null. Hence, out of above 3, CustomerID will be set as Primary and all 3 together be referred as Candidate key.