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 Key||Primary Key|
|Meaning||One or more sets of attributes are used to identify records in a table. Can have one or more per table||Specific candidate key chosen to uniquely identify the table. Can only have one per table|
|NULL status||Can have NULL values||Can’t have a NULL value|
|Uniqueness||Unique, but many choices for a candidate key||Unique, but only one primary key can be chosen from candidate keys|
|Usage||Can be used as a primary key, or as an alternate key for other columns in the table||Establish relationships between tables and maintain the uniqueness of each record|
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.
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.
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.
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.
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: 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.