© Yurich / Shutterstock.com

When using relational databases with systems such as MySQL, keys are an essential part of your operations. Generally speaking, keys are used to identify rows in a database and to express relationships between data values. There are many kinds of keys used in these databases, but the most common and essential are the primary and candidate keys. Find out what these terms mean and how they’re used in this article.

Candidate Key Vs. Primary Key: Side-by-Side Comparison

Candidate KeyPrimary Key
MeaningOne or more sets of attributes are used to identify records in a table. Can have one or more per tableSpecific candidate key chosen to uniquely identify the table. Can only have one per table
NULL statusCan have NULL valuesCan’t have a NULL value
UniquenessUnique, but many choices for a candidate keyUnique, but only one primary key can be chosen from candidate keys
UsageCan be used as a primary key, or as an alternate key for other columns in the tableEstablish relationships between tables and maintain the uniqueness of each record
Candidate Key Vs. Primary Key
In this database, Customer_id is the primary key of the table Customer.

©Joe Techapanupreeda/Shutterstock.com

Candidate Vs. Primary Key: What’s the Difference?

The main differences have been summarized in the table above, but let’s take a closer look.

Meaning

In simple terms, a candidate key is a field, or combination of fields, that could be used to uniquely identify each record in the table. As such, you can have several potential candidate keys in a table. For example, let’s consider a table with columns “ID”, “Name”, “Age”, ‘Date of Birth”, “Weight”, “Height”, and “Address”. In this case, we could uniquely identify each record with either the “ID” field or potentially the “Name” and “Date of Birth” fields since these will likely be unique.

On the other hand, there can only be one primary key per table. This is because it’s a specific key chosen out of the candidate keys to represent the table. Continuing with the previous example, we can see that “ID” is a good choice for a primary key, since this will be unique to each person in the table, whereas every other field could potentially contain duplicate values.

NULL status

This is another area where the keys differ. Candidate keys can potentially contain null values, but only if they satisfy the criteria for a candidate key – the uniqueness and minimality criteria. This means that, for the field to be a potential candidate key, it must be able to uniquely identify the table by minimal attributes. By minimal, we mean that, if we removed an attribute from the key, it would lose its uniqueness. As such, it contains the minimum number of attributes to be a unique identifier. If we look at the previous example, we can take either the “Name” and “Date of Birth” fields as a candidate key, and also the “ID” field. This is because, even if other attributes are removed, these can still be used to uniquely identify the table. As such, these fields can contain null values in theory.

Primary keys, however, cannot contain null values. This is because, if the field contained null values, these would not be unique values. Therefore, the primary key would not be successful in uniquely identifying the table.

Uniqueness

Both types of keys are unique, but there are some differences in their uniqueness. There can be several potential candidate keys, as long as they can each be used to uniquely identify a table, so in this way, candidate keys are not strictly unique in that there can be more than one.

In contrast, there can only be one primary key identified per table, either one column or a combination. Therefore, the primary key is also unique and will be used alone for each table.

Usage

Both kinds of keys can potentially be used to uniquely identify a table, but their usage does vary. Candidate keys can be used for other database purposes. These include:

  • Indexing, in order to speed up retrieval and search operations.
  • Enforcing constraints, such as the unique constraint. This means that no two rows in the table can have the same value for the columns identified with this key.
  • Optimizing queries, so that complex queries can be made more efficient.
  • Data modeling, such as identifying key attributes and optimizing the database performance.

Primary keys have some common uses, such as indexing, data modeling, and enforcing uniqueness, but they can also be used for referential integrity. Simply put, this means that the primary key can be used in other tables as something called a foreign key. This is a key in another table that references the primary key, ensuring that the values in this column correspond to the values in the primary key column. In this way, primary keys can be used to maintain database consistency.

Candidate Key Vs. Primary Key
The correct layout, with primary keys, is very important in database planning.

©Wichy/Shutterstock.com

Candidate Key Vs. Primary Key: Wrapping Up

To conclude, candidate keys are potential primary keys used for indexing purposes, query optimization, data modeling, and enforcing constraints. Primary keys are used similarly, but also for referential integrity and maintaining the uniqueness of a table. While you can have multiple candidate keys per table, you can only have one primary key. You can also have null values in candidate key fields, but not in primary key fields.

Candidate Key Vs. Primary Key: When Should You Use Each One? FAQs (Frequently Asked Questions) 

What is a candidate key?

A candidate key is one or more columns in a table that can uniquely identify the table rows. They are potential primary keys, and there can be one or more per table.

 

What are candidate keys used for?

They’re used to uniquely identify rows, as well as for indexing, query optimization, data modeling and enforcing constraints.

What is a primary key?

A primary key is one or more columns in a table that uniquely identifies the rows, and is chosen from the candidate keys. You can only have one per table.

What are primary keys used for?

They’re used to uniquely identify a table and enforce referential integrity between tables, as well as for the same purposes as candidate keys.

What is a foreign key?

A foreign key is one or more columns in a separate table that references the primary key of the first table. This helps to enforce referential integrity and establish a relationship between tables. When values of the primary key are updated, these changes are reflected in the foreign key.

About the Author

More from History-Computer

  • GeeksforGeeks Available here: https://www.geeksforgeeks.org/difference-between-primary-and-candidate-key/
  • Javatpoint Available here: https://www.javatpoint.com/primary-key-vs-candidate-key