Chapter 3: Designing Bulletproof Databases
115
Uniquely identifying each record in a table is precisely what a primary key field does. For example,
using the Collectible Mini Cars as an example, the CustomerID field (a unique number that you
assign to each customer placing an order) is the primary key in tblCustomers — each record in
the table has a different CustomerID number. (No two records have the same number.) This is
important for several reasons:
l You don’t want to have two records in tblCustomers for the same customer, because
this can make updating the customer’s record virtually impossible.
l You want assurance that each record in the table is accurate, so that the information
extracted from the table is accurate.
l You don’t want to make the table (and its records) any larger than necessary. Adding
redundant or duplicate fields and records just complicates the database without adding
value.The ability to assign a single, unique value to each record makes the table clean and reliable. This
is known as entity integrity. By having a different primary key value in each record (such as the
CustomerID in tblCustomers), you can tell two records (in this case, customers) apart, even if
all other fields in the records are the same. This is important because you can easily have two indi-
vidual customers with a common name, such as Fred Smith, in your table.
Theoretically, you could use the customer’s name and address, but two people named Fred D.
Smith could live in the same town and state, or a father and son (Fred David Smith and Fred
Daniel Smith) could live at the same address. The goal of setting primary keys is to create individ-
ual records in a table that guarantees uniqueness.
If you don’t specify a primary key when creating Access tables, Access asks whether you want one.
If you say yes, Access uses the AutoNumber data type to create a primary key for the table. An
AutoNumber field is automatically inserted each time a record is added to the table, and can’t be
changed once its value has been established. Furthermore, once an AutoNumber value has
appeared in a table, the value will never be reused, even if the record containing the value is
deleted and the value no longer appears in the table. In fact, because an AutoNumber field is
added to a new record before any of the other data, if the new row is not saved for some reason,
the new AutoNumber is never used in the table at all.
Deciding on a primary key
As you learned previously, a table normally has a unique field (or combination of fields) — the pri-
mary key for that table — which makes each record unique. The primary key is an identifier that is
often a text or AutoNumber data type. To determine the contents of this ID field, you specify a
method for creating a unique value for the field. Your method can be as simple as letting Access
automatically assign an AutoNumber value or using the first letter of the real value you’re tracking
along with a sequence number (such as A001, A002, A003, B001, B002, and so on). The method
may rely on a random set of letters and numbers for the field content (as long as each field has a
unique value) or a complicated calculation based on information from several fields in the table.