Candidate key

What is a candidate key?

A candidate key is any column, or set of columns, that can uniquely identify every row in a table. It is a candidate in the literal sense: one of the identities the table could adopt as its own. A single table usually has more than one.

Take a customer table. The internal customer_id, the email address, and the VAT number can each pick out one specific row, as long as every customer carries a value and no two customers share it. Each of those is a candidate key. From that shortlist you elect one primary key and keep the others as backup identities.

The two tests: unique and minimal

A set of columns qualifies as a candidate key when it passes two checks.

Uniqueness. No two rows may share the same combination of values, and every row must carry a value in those columns. A column full of distinct values but scattered with blanks cannot identify the blank rows, so it fails. This is why a candidate key has to be both unique and not null.

Minimality. No smaller part of the set is unique on its own. If email already identifies a customer, then email plus postcode is still unique, but postcode adds nothing to the identity. Remove it and the key still works, so the pair is not a candidate key.

In SQL you write these identities as constraints. The primary key together with any extra unique constraints spells out a table's candidate keys.

CREATE TABLE customers (
  customer_id  integer  PRIMARY KEY,
  email        text     NOT NULL UNIQUE,
  vat_number   text     NOT NULL UNIQUE,
  name         text     NOT NULL
);

Here customer_id is the elected primary key. email and vat_number are separate candidate keys, each held unique and not null by the database.

Candidate key versus primary key

A table with several candidate keys picks exactly one to be its primary key: the identity that other tables reference through a foreign key. PostgreSQL states it plainly. A table can hold any number of unique constraints, but only one of them may be marked as the primary key. The rest are still candidate keys, they simply were not chosen.

The choice is practical, not theoretical. A stable internal number makes a better primary key than an email address, because an email can change and a primary key should not. It is also why many warehouses mint a surrogate key instead of leaning on a business key such as a VAT number. The candidate keys you pass over stay useful as unique constraints that keep duplicates out.

Where candidate keys matter

The idea does real work well before anyone writes a CREATE TABLE statement.

• Data modelling. Listing the candidate keys forces the question of which fields the business itself treats as an identity, which in turn shapes how you split and relate tables.

• Deduplication. A candidate key such as email or VAT number is exactly the field you match records on to catch the same customer entered twice.

• Master data management. When you reconcile an entity across systems, its candidate keys are the columns you agree to keep unique and mandatory so the record stays recognisable everywhere.