Database Reference
In-Depth Information
•
Its value is not optional in whole or in part.
A value that is optional implies that it
may be null at some point. You can infer, then, that an optional value automatically
violates the previous element and is, therefore, unacceptable. (This caveat is espe-
cially applicable when you want to use two or more fields as a candidate key.)
•
It comprises a minimum number of fields necessary to define uniqueness.
You can
use a combination of fields (treated as a single unit) to serve as a candidate key, so
long as each field contributes to defining a unique value. Try to use as few fields as
possible, however, because overly complex candidate keys can ultimately prove to
be difficult to work with and difficult to understand.
•
Its values must uniquely and exclusively identify each record in the table.
This ele-
ment helps you guard against duplicate records and ensures that you can accurately
reference any of the table's records from other tables in the database.
•
Its value must exclusively identify the value of each field within a given record.
This element ensures that the table's candidate keys provide the only means of
identifying each field value within the record. (You'll learn more about this partic-
ular element in the section on primary keys.)
•
Its value can be modified only in rare or extreme cases.
You should never change
the value of a candidate key unless you have an absolute and compelling reason to
do so. A field is likely to have difficulty conforming to the previous elements if
you can change its value arbitrarily.
Establishing a candidate key for a table is quite simple: Look for a field or set of fields that
conformstoalloftheElementsofaCandidateKey.You'llprobablybeabletodefinemore
than one candidate key for a given table. Loading a table with sample data will give you
the means to identify potential candidate keys accurately. (You used this same technique in
the previous chapter.)
See if you can identify any candidate keys for the table in
Figure 8.1
.
