Microsoft Access 2010 Bible

Part I: Access Building Blocks

76

FIGURE 2.25

Using a combo box as a lookup control to restrict user input on a field

Setting the Primary Key

Every table should have a primary key — one or a combination of fields with a unique value for

each record. (This principle is called entity integrity in the world of database management.) In

tblCustomers, the CustomerID field is the primary key. Each customer has a unique

CustomerID value so that the database engine can distinguish one record from another.

CustomerID 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.

Choosing a primary key

Without the CustomerID field, you’d have to rely on another field or combination of fields for

uniqueness. You couldn’t use the Company field because two customers could easily have the

same company name. In fact, you couldn’t even use the Company and City fields together (in a

multi-field key), for the same reason — it’s entirely possible two customers with the same name

exist in the same city. You need to come up with a field or combination of fields that makes every

record unique.

The easiest way to solve this problem is to add an AutoNumber field to serve as the table’s pri-

mary key. The primary key in tblCustomers is CustomerID, an AutoNumber field.

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

it as the table’s primary key. AutoNumber fields make very good primary keys because Access cre-

ates the value for you, the number is never reused within a table, and you can’t change the value of

an AutoNumber field.