Difference Between Primary Key vs Candidate Key

A primary key and a candidate key are both types of database constraints that are used to uniquely identify rows in a table. However, there are a few key differences between the two.

What is a Primary Key?

A primary key is a column or set of columns in a relational database table that is used to uniquely identify each row in the table. It is used to enforce integrity constraints and prevent duplicate values. The primary key is chosen when the table is created and it cannot contain null values. The primary key is usually used as a reference for foreign keys in other tables to create relationships between them. It is also used to create indexes that can improve the performance of queries and transactions.

What is a Candidate Key?

A candidate key is a column or set of columns in a relational database table that can be used to uniquely identify each row in the table, but is not designated as the primary key. A table can have multiple candidate keys, but only one primary key. A candidate key is a potential primary key, which can be used as a unique identifier for a row in a table, but it is not chosen to be the primary key. Each candidate key must be unique and cannot contain null values. A table can have multiple candidate keys, but only one can be chosen as the primary key, the other ones are called alternate keys.

Example of Primary Key & Candidate Key

Here is an example of a primary key and candidate key:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,
    UNIQUE (Email),
    UNIQUE (PhoneNumber)
);

In this example, the EmployeeID column is the primary key for the table, and it is used to uniquely identify each row in the table. The Email and PhoneNumber columns are candidate keys, as they can also be used to uniquely identify each row in the table, but they are not designated as the primary key. The UNIQUE constraint is used to indicate that the values in these columns must be unique.

In this example, the primary key is EmployeeID and the candidate keys are Email and PhoneNumber.

See this example in table format.

EmployeeID	FirstName	LastName	Email	PhoneNumber
1	John	Doe	johndoe@example.com	555-555-5555
2	Jane	Smith	janesmith@example.com	555-555-5556
3	Michael	Johnson	michaelj@example.com	555-555-5557

In this example, the EmployeeID column is the primary key and is used to uniquely identify each row in the table. The Email and PhoneNumber columns are candidate keys and can also be used to uniquely identify each row in the table, but they are not designated as the primary key. The values in these columns must be unique as per the UNIQUE constraint mentioned in the create table statement.

Primary Key vs Candidate Key

First, a primary key is a single field or column in a table that is used to uniquely identify each row. A candidate key, on the other hand, is a set of one or more fields or columns that can be used to uniquely identify each row. This means that a table can have multiple candidate keys, but only one primary key.

Second, a primary key is automatically indexed by the database, which means that the database can quickly and efficiently find the rows that match a given primary key value. A candidate key, on the other hand, is not automatically indexed, so the database may need to do a more expensive scan of the table to find rows that match a given candidate key.

Third, a primary key is a mandatory constraint, which means that every row in the table must have a non-null, unique value in the primary key column. A candidate key, on the other hand, is optional and can be applied to a column that already contains null or duplicate values.

Overall, both primary keys and candidate keys serve similar purposes, but primary keys are typically used as the main means of identifying and relating rows in a database, while candidate keys are used as alternative means of identifying rows in case the primary key is not available.

Difference between Primary Key and Candidate Key (Comparison Table)

Primary Key	Candidate Key
A primary key is a column or set of columns that uniquely identifies each row in a table.	A candidate key is a column or set of columns that could be used as a primary key, but is not necessarily chosen to be so.
Primary key is used to enforce the integrity of the data and to create relationships between tables.	A candidate key is a column or set of columns that could be used as a primary key, but is not necessarily chosen to be so.
Only one primary key can be created for a table.	A table can have multiple candidate keys.
A primary key value cannot be NULL.	A candidate key value can be NULL.
Primary key creates clustered index on the table.	Candidate key does not create any index on the table.

More to Read

• Relational Database Benefits and Limitations
• Relational Vs Non Relational Database
• Data Warehouse vs Database
• Dataset vs Database
• Database vs DataFrame
• Primary Key vs Foreign Key
• 13 Examples of Relational Database
• Relational Database Vs. Object-Oriented Database
• 9 Types of Databases
• Distributed Database
• Operational Database
• Personal Database
• Graph Databases
• Centralized Database