Database Reference
In-Depth Information
Artificial Candidate Keys
When you determine that a table does not contain a candidate key, you can create and use
an artificial (or surrogate ) candidate key. (It's artificial in the sense that it didn't occur
“naturally” in the table; you have to manufacture it.) You establish an artificial candidate
key by creating a new field that conforms to all of the Elements of a Candidate Key and
then adding it to the table; this field becomes the official candidate key.
You can now solve the problem in the PARTS table. Create an artificial candidate key
called P ART N UMBER and assign it to the table. (The new field will automatically conform
to the Elements of a Candidate Key because you're creating it from scratch.) Figure 8.4
shows the revised structure of the PARTS table.
Figure 8.4. The PARTS table with the artificial candidate key P ART N UMBER
When you've established an artificial candidate key for a table, mark the field name with
a “CK” in the table structure, just as you did for the EMPLOYEES table in the previous
example.
You may also choose to create an artificial candidate key when it would be a stronger (and
thus, more appropriate) candidate key than any of the existing candidate keys. Assume
you're working on an EMPLOYEES table and you determine that the only available can-
didate key is the combination of the E MP F IRST N AME and E MP L AST N AME fields. Although
this may be a valid candidate key, using a single-field candidate key might prove more ef-
ficient and may identify the subject of the table more easily. Let's say that everyone in the
organization is accustomed to using a unique identification number rather than a name as
a means of identifying an employee. In this instance, you can choose to create a new field
named E MPLOYEE ID and use it as an artificial candidate key. This is an absolutely accept-
able practice—do this without hesitation or reservation if you believe it's appropriate.
Search WWH ::




Custom Search