THE RELATIONAL DATABASE MODEL: INTRODUCTION - Fundamentals of Database Management Systems - page 110

relation that does not include a Salesperson Number attribute, but instead has a First
Name attribute, a Middle Name attribute, and a Last Name attribute. The primary
key might then be the combination of the First, Middle, and Last Name attributes
(assuming this would always produce a unique combination of values. If it did not,
then a fourth attribute could be added to the relation and to the primary key as a
sequence field to produce, for example, John Alan Smith #1, John Alan Smith #2,
and so forth). Some attribute or combination of attributes of a relation has to be
unique and this can serve as the unique primary key, since, by definition, no two
rows can be identical. In the worst case, all of the relation's attributes combined
could serve as the primary key if necessary (but this situation is uncommon in
practice).

Candidate Keys If a relation has more than one attribute or minimum group of
attributes that represents a way of uniquely identifying the entities, then they are
each called a candidate key. (Actually, if there is only one unique attribute or
minimum group of attributes it can also be called a candidate key.) For example, in
a personnel relation, an employee number attribute and a Social Security Number
attribute (each of which is obviously unique) would each be a candidate key of that
relation. When there is more than one candidate key, one of them must be chosen to
be the primary key of the relation. That is where the term ''candidate key'' comes
from, since each one is a candidate for selection as the primary key. The decision of
which candidate key to pick to be the primary key is typically based on which one
will be the best for the purposes of the applications that will use the relation and the
database. Sometimes the term alternate key is used to describe a candidate key that
was not chosen to be the primary key of the relation, Figure 5.3.

FIGURE 5.3
Candidate keys become either primary or
alternate keys

Alternate
Key

The Winner and
Primary Key

Alternate
Key