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.
Therefore, a relation can have multiple candidate keys, each with a different number of attributes.
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 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.
The definition of candidate keys can be illustrated with the following (abstract) example.
Consider a relation variable (relvar) R with attributes (A, B, C, D) that has only the following two legal values r1 and r2: Here r2 differs from r1 only in the A and D values of the last tuple.
For r1 the following sets have the uniqueness property, i.e., there are no two distinct tuples in the instance with the same attribute values in the set: For r2 the uniqueness property holds for the following sets; Since superkeys of a relvar are those sets of attributes that have the uniqueness property for all legal values of that relvar and because we assume that r1 and r2 are all the legal values that R can take, we can determine the set of superkeys of R by taking the intersection of the two lists: Finally we need to select those sets for which there is no proper subset in the list, which are in this case: These are indeed the candidate keys of relvar R. We have to consider all the relations that might be assigned to a relvar to determine whether a certain set of attributes is a candidate key.
For example, if we had considered only r1 then we would have concluded that {A,B} is a candidate key, which is incorrect.
Note that the existence of a proper subset of a set that has the uniqueness property cannot in general be used as evidence that the superset is not a candidate key.
In particular, note that in the case of an empty relation, every subset of the heading has the uniqueness property, including the empty set.
To this end we need to define the attribute closure
It is quite simple to find a single candidate key.
Actually we can detect every candidate key with this procedure by simply trying every possible order of removing attributes.
That is, many attribute orders will lead to the same candidate key.
There is a fundamental difficulty for efficient algorithms for candidate key computation: Certain sets of functional dependencies lead to exponentially many candidate keys.
That is, the best we can expect is an algorithm that is efficient with respect to the number of candidate keys.
The following algorithm actually runs in polynomial time in the number of candidate keys and functional dependencies:[4] The idea behind the algorithm is that given a candidate key
, the reverse application of the functional dependency yields the set
(The algorithm checks this case using the 'found' variable.)