Microsoft Access 2010 Bible

(Rick Simeone) #1

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.
Free download pdf