Candidate Key

A candidate key is a set of attributes, taken from the header of a relational table; with the property that no two records/tuples/rows in the table may have the same CandidateKey. In other words, were a table to be projected by any of its candidate keys, the resulting table would always have the same cardinality. A table may have multiple candidate keys; but it must have at least one (consisting of all attributes in the header); otherwise it would be a bag and not a set.

Generally, one is only concerned with independent candidate keys; if a relation with attributes (columns) {NAME, RANK, SERIAL_NUMBER} were to have {SERIAL_NUMBER} as a candidate key; then {NAME, SERIAL_NUMBER}, {RANK, SERIAL_NUMBER}, and {NAME, RANK, SERIAL_NUMBER} would also be (trivial) candidate keys; however since these are covered by the {SERIAL_NUMBER} CandidateKey we usually don't care about the others.

Many RDMBS's limit the number of candidate keys to one (or otherwise designate one such key as special); this is often called the PrimaryKey. In some implementations, additional keys can be simulated using indexes with a uniqueness constraint.

It's worth noting that keys are not a part of the fundamental RelationalModel, which requires only that tuples (rows) be unique. Candidate keys are just a way of identifying a subset of the attributes that, taken alone, satisfy this requirement.

A candidate key can also be considered as the determinant of a FunctionalDependency, a more general concept which, like the candidate key notion, is useful in the analysis and design of database schemas and query algorithms.