Editing Candidate key (section)

{{Short description|Set of columns in a relational database}}
A '''candidate key''', or simply a '''key''', of a [[relation schema|relational database]] is any set of [[Column (database)|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]],<ref>{{cite web |url=https://www.dcs.warwick.ac.uk/~hugh/TTM/CJD-on-EFC's-First-Two-Papers.pdf |title=Codd's First Relational Papers: A Critical Analysis |last=Date |first=Christopher |date=2015 |website=warwick.ac.uk |publisher= |access-date=2020-01-04 |quote=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''.}}</ref> 
i.e., a superkey that does not contain a smaller one. Therefore, a relation can have multiple candidate keys, each with a different number of attributes.<ref>{{Cite web |title=database - Can a relation have Candidate Keys with different lengths? |url=https://stackoverflow.com/questions/45533608/can-a-relation-have-candidate-keys-with-different-lengths |access-date=2023-03-23 |website=Stack Overflow |language=en}}</ref>

Specific candidate keys are sometimes called ''[[primary key]]s'', ''secondary keys'' or ''alternate keys''.
The columns in a candidate key are called '''prime attributes''',<ref>{{Cite journal|last=Saiedian|first=H.|date=1996-02-01|title=An Efficient Algorithm to Compute the Candidate Keys of a Relational Database Schema|url=https://academic.oup.com/comjnl/article-lookup/doi/10.1093/comjnl/39.2.124|journal=The Computer Journal|language=en|volume=39|issue=2|pages=124–132|doi=10.1093/comjnl/39.2.124|issn=0010-4620|url-access=subscription}}</ref> 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 is not 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]].