Office 2007 Bible

Setting the Primary Key

Every table should have a primary key—one or more fields with a unique value for each record. (This principle is called entity integrity in the world of database management.) In tblContacts, the ContactID field is the primary key. Each contact has a different ContactID value so that you can identify one from the other. ContactID 17 refers to one and only one record in the Contacts table. If you don’t specify a primary key (unique value field), Access can create one for you.

Understanding unique values

Without the ContactID field, you’d have to rely on another field or combination of fields for uniqueness. You couldn’t use the LastName field because two customers could easily have the same last name. In fact, you couldn’t even use the FirstName and LastName fields together (multi-field key), for the same reason—two people could be named James Smith. You need to come up with a field that makes every record unique. Looking at the table, you may think that you could use a combination of the LastName, FirstName, and Company fields, but theoretically, it’s possible that two people working at the same company have the same name.

The easiest way to solve this problem is to add an AutoNumber field for the express purpose of using it as the table’s primary key. This is exactly the situation with the Contacts table. The primary key of this table is ContactID, an AutoNumber field.

If you don’t designate a field as a primary key, Access can add an AutoNumber field ...