Part I: Access Building Blocks
118
Designating a primary key
From the preceding sections, you’re aware that choosing a table’s primary key is an important step
toward bulletproofing a database’s design. When properly implemented, primary keys help stabi-
lize and protect the data stored in your Access databases. As you read the following sections, keep
in mind that the cardinal rule governing primary keys is that the values assigned to the primary
key field within a table must be unique. Furthermore, the ideal primary key is stable.Single-field versus composite primary keys
Sometimes, when an ideal primary key doesn’t exist within a table as a single value, you may be
able to combine fields to create a composite primary key. For example, it’s unlikely that a first name
or last name alone is enough to serve as a primary key, but by combining first and last names with
birth dates, you may be able to come up with a unique combination of values to serve as the pri-
mary key. As you’ll see in the “Creating relationships and enforcing referential integrity” section,
later in this chapter, Access makes it very easy to combine fields as composite primary keys.There are several practical considerations when using composite keys:l None of the fields in a composite key can be null.l (^) Sometimes composing a composite key from data naturally occurring within the
table can be difficult. Sometimes records within a table differ by one or two fields, even
when many other fields may be duplicated within the table.
l Each of the fields can be duplicated within the table, but the combination of compos-
ite key fields cannot be duplicated.
However, as with so many other issues in database design, composite keys have a number of
issues:
l Composite keys tend to complicate a database’s design. If you use three fields in a par-
ent table to define the table’s primary key, the same three fields must appear in every child
table.
l (^) Ensuring that a value exists for all the fields within a composite key (so that none of
the fields is null) can be quite challenging.
Tip
Most developers avoid composite keys unless absolutely necessary. In many cases, the problems associated
with composite keys greatly outweigh the minimal advantage of using composite keys generated from data
within the record.
Natural versus surrogate primary keys
Many developers maintain that you should use only natural primary keys. A natural primary key is
derived from data already in the table, such as a Social Security number or employee number. If no
single field is enough to uniquely identify records in the table, these developers suggest combining
fields to form a composite primary key.