Candidate Key

A relation may contain not one but several attributes whose values are always distinct — and any one of them, on its own, is enough to uniquely identify the tuples. Attributes with this power are called candidate keys. The name is exact: each such attribute is a candidate for the role of primary key, the one identifier the database designer will eventually pick.

The GUARDIAN relation (Figure 7.4) shows the idea cleanly. It has four attributes, and among them two always take unique values:

• GUID — no two guardians will ever have the same GUID;
• GPhone — no two guardians will have the same phone number.

Either attribute alone can tell every guardian's tuple apart from every other. Both, therefore, are candidate keys of GUARDIAN — two rival candidates for the position of primary key.

The other two attributes of GUARDIAN fail the test. A guardian's name can repeat (two different guardians may share a name), and so can an address (two guardians can live at the same address), so neither can be trusted to single out one tuple.

What earns an attribute candidacy is worth restating as a checklist:

• its data values are distinct across all tuples — no value ever repeats in that column; and
• because identification is its job, a value must actually be there to identify with — an attribute whose values can be absent cannot pin down a row. …