Candidate key

A candidate key, or simply a key, of a relational database is any set of columns that have a unique combination of values in each row, with the additional constraint that removing any column could produce duplicate combinations of values.

A candidate key is a minimal superkey,[1] i.e., a superkey that doesn't contain a smaller one. Therefore, a relation can have multiple candidate keys, each with a different number of attributes.[2]

Specific candidate keys are sometimes called primary keys, secondary keys or alternate keys. The columns in a candidate key are called prime attributes,[3] and a column that does not occur in any candidate key is called a non-prime attribute.

Every relation without NULL values will have at least one candidate key: Since there cannot be duplicate rows, the set of all columns is a superkey, and if that isn't minimal, some subset of that will be minimal.

There is a functional dependency from the candidate key to all the attributes in the relation.

The superkeys of a relation are all the possible ways we can identify a row. The candidate keys are the minimal subsets of each superkey and as such, they are an important concept for the design of database schema.

  1. ^ Date, Christopher (2015). "Codd's First Relational Papers: A Critical Analysis" (PDF). warwick.ac.uk. Retrieved 2020-01-04. Note that the extract allows a "relation" to have any number of primary keys, and moreover that such keys are allowed to be "redundant" (better: reducible). In other words, what the paper calls a primary key is what later (and better) became known as a superkey, and what the paper calls a nonredundant (better: irreducible) primary key is what later became known as a candidate key or (better) just a key.
  2. ^ "database - Can a relation have Candidate Keys with different lengths?". Stack Overflow. Retrieved 2023-03-23.
  3. ^ Saiedian, H. (1996-02-01). "An Efficient Algorithm to Compute the Candidate Keys of a Relational Database Schema". The Computer Journal. 39 (2): 124–132. doi:10.1093/comjnl/39.2.124. ISSN 0010-4620.