Chapter 3: Designing Bulletproof Databases
119
However, there are many situations where no “perfect” natural key exists in database tables.
Although a field like SocialSecurityNumber may seem to be the ideal primary key, there are a
number of problems with this type of data:l The value is not universal. Not everyone has a Social Security number.l (^) The value may not be known at the time the record is added to the database. Because
primary keys can never be null, provisions must be made to supply some kind of “tempo-
rary” primary key when the Social Security number is unknown, and then other provi-
sions must be made to fix up the data in the parent and child tables once the value
becomes known.
l Values such as Social Security number tend to be rather large. A Social Security number
is at least nine characters, even omitting the dashes between groups of numbers. Large pri-
mary keys unnecessarily complicate things and run more slowly than smaller primary keys.
l (^) Legal and privacy issues inhibit its use. A Social Security number is considered “per-
sonally identifiable information” and (in the United States) its use is limited under the
Social Security Protection Act of 2005.
Caution
By far the largest issue is that adding a record to a table is impossible unless the primary key value is known at
the time the record is committed to the database. Even if temporary values are inserted until the permanent
value is known, the amount of fix-up required in related tables can be considerable. After all, unless Cascade
Update is enabled on the relationship, you can’t change the value of a primary key if related child records exist
in other tables.
Although an AutoNumber value does not naturally occur in the table’s data, because of the consider-
able advantages of using a simple numeric value that is automatically generated and cannot be deleted
or changed, in most cases an AutoNumber is the ideal primary key candidate for most tables.
Creating primary keys
A primary key is created by opening a table in Design view, selecting the field (or fields) that you
want to use as a primary key, and clicking the Primary Key button on the toolbar (the button with
the key on it). If you’re specifying more than one field to create a composite key, hold down the Ctrl
key while using the mouse to select the fields before clicking on the Primary Key toolbar button.
Cross Reference
Setting a table’s primary key is covered in detail in Chapter 2.
Creating relationships and
enforcing referential integrity
The Relationships window Database Ribbon icon lets you specify the relationships and referential
integrity rules you want to apply to the tables involved in a relationship. Creating a permanent,
managed relationship that ensures referential integrity between Access tables is easy: