What is the difference between primary key and candidate key?

by

To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.

A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.

In MySQL, as well as in database management systems in general, the primary key and candidate key are both key concepts (pun intended!) in database design, but they serve slightly different purposes:

  1. Primary Key:
    • A primary key is a column or a set of columns in a table that uniquely identifies each row in the table.
    • It must contain unique values, meaning no two rows in the table can have the same primary key value.
    • Additionally, it cannot contain NULL values.
    • In MySQL, a primary key is typically created using the PRIMARY KEY constraint.
  2. Candidate Key:
    • A candidate key is also a column or a set of columns in a table that can uniquely identify each row in the table.
    • Like the primary key, it must contain unique values.
    • However, unlike the primary key, a candidate key can contain NULL values.
    • Essentially, any key that is unique and can uniquely identify a row can be considered a candidate key.
    • In practice, one of the candidate keys is chosen as the primary key for the table.

So, the primary difference lies in the fact that the primary key is chosen from among the candidate keys to uniquely identify rows, and it cannot contain NULL values, whereas a candidate key can include NULL values.