What Is a Candidate Key?

A candidate key is the combination of the least number of fields necessary to identify and locate a unique record in a database. The primary key of a table is generally the simplest candidate key that occurs in the table.

Significance

In a relational database, data is separated into tables based on the subject of the data, for example "Contacts." Each table has many attributes, also known as fields, such as a contact's phone number, name or email address. Tables are then linked to related data in other tables by using reference keys. For example an order is related to a specific customer with the foreign key field "CustomerID."

Function

Keys serve two roles in relational databases: to relate the data in tables and to differentiate records from one another. Primary keys are one or many fields used to link tables together. For example, the primary key of the "Customer" table "CustomerID" is the foreign key on the "Order" table that links the order to a specific customer.

Features

Each table can have several candidate keys; for example, the "Customer" table may have two candidate keys. "CustomerId" as a single field candidate key and the combination of "FirstName," "LastName" and "Email" may be another. One of the candidate keys is selected as the primary key for the table. Typically the best choice uses a single field such as the "CustomerId" field, as it optimizes the performance of queries.

References

• Database Dev Co: What is a Key?
• Relational Database Management Systems: Database Keys
• University of Winnipeg: Candidate Key