The candidate key contains duplicate values

Difference between primary key and candidate key

Primary key vs candidate key

Although the primary key is chosen from the candidate keys, there are some differences between the primary key and the other candidate keys, which are detailed in this article. Database design is one of the most important activities that should be performed when managing and storing data. Various tables with many relationships must be created during this design process. To access these tables in a database, modern database design languages ​​such as: Different key types are used, for example MYSQL, MSAccess, SQLite, etc. Of these keys, candidate keys and primary keys have become indispensable for database design.

What is a candidate key?

Candidate key is a single column or a group of columns in a table in a database that can be used Uniquely identify each database entry without referencing any other data. Each table in a database can have one or more candidate keys. A set of candidate keys can be created using functional dependencies. A candidate key contains several important functions. You are;

• Candidate keys should be unique within the domain and should not contain NULL values.

• The candidate key should never change, and it must contain the same value for a specific occurrence of an entity.

The main purpose of a candidate key is to help identify a single row out of millions of rows in a large table. Each candidate key is qualified to become a primary key. However, of all the candidate keys, the most important and specific candidate key becomes the primary key of a table, and it is the best among the candidate keys.

What is a primary key?

A primary key is The best candidate key for a table that uniquely identifies records which are stored in a table. When creating a new table in a database, we are asked to choose a primary key. Therefore, choosing a primary key for a table is the most critical decision a database designer should make. The main limitation to consider when deciding on a primary key is that the selected column of the table should contain only unique values ​​and not NULL values. Common primary keys used when creating tables include the Social Security Number (SSN), ID, and National Identity Card Number (NIC).

The programmer should remember to choose a primary key carefully as it is difficult to change. Therefore, according to the programmers, it is best to create a primary key using an internally generated primary key, e.g. When we try to insert a record into a table with a primary key that duplicates an existing record, the insert fails. The value of the primary key shouldn't change constantly, so it's more important to keep a static primary key.

A primary key is the best candidate key.

What is the difference between primary key and candidate key?

• A candidate key is the column that is uniquely qualified, while the primary key is the column that uniquely identifies a record.

• A table without a candidate key does not represent a relationship.

• There are many candidate keys for a table in a database, but there should only be one primary key for a table.

• Although the primary key is one of the candidate keys, sometimes it is the only candidate key.

• Once a primary key is selected, the other candidate keys become unique keys.

• A candidate key can practically contain NULL values, although it currently does not contain any value. Therefore, the candidate key is not qualified for a primary key because the primary key should not contain NULL values.

• It is also possible that candidate keys that are currently unique contain duplicate values ​​that prevent the candidate key from becoming a primary key.


Primary key vs. candidate key

Candidate keys and primary keys are essential keys used in database design to uniquely identify data in a record and to establish relationships between tables in a database. A table should only contain one primary key and can contain multiple candidate keys. Most databases today can generate their own primary key automatically. Therefore, the primary key and the candidate keys provide extensive support for the database management systems.

Images courtesy of:

  1. Primary key by SqlPac (CC BY-SA 3.0)