Primary Key vs Candidate Key

John Spacey, updated on January 15, 2023

A primary key is a field or set of fields that uniquely identifies a row in a table that is explicitly defined with a primary key constraint.

A candidate key is a field or set of fields that uniquely identifies a row in a table.

The Difference

A table can only have one primary key. Tables may have many candidate keys that uniquely identify each row. The primary key is the candidate key that is selected by the data architect and defined as a primary key constraint in a table definition.

It is a good practice to define a primary key for every table and to always use the primary key to relate to a row in a table with a foreign key constraint.

If tables used different candidate keys to relate to a table, maintaining referential integrity might become complex and error prone as rows are deleted and updated.

Example

A table of licensed drivers in a country includes multiple candidate keys such as drivers license number and mobile phone number. The data architect defines a field called ID as a sequence number and defines it as the primary key. This tells all tables in the database to only use ID to relate to rows in the table. When drivers license numbers and mobile phone numbers are updated there is no need to update relations. The ID field never changes and is ideal as a primary key. As the ID is a short sequence number, it is also efficient for use in indexes.

Primary Key vs Candidate Key
	Primary Key	Candidate Key
Definition	A field or set of fields that uniquely identifies a row in a table that is explicitly defined with a primary key constraint.	A field or set of fields that uniquely identifies a row in a table.