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 and designate it as the table’s primary key. This field contains a unique number for each record in the table, and Access maintains it automatically.
Generally speaking, you may want to create and maintain your own primary key, even if you always use AutoNumber fields as primary keys:
A primary key is always an index.
An index maintains a presorted order of one or more fields that greatly speeds up queries, searches, and sort requests.
When you add new records to your table, Access checks for duplicate data and doesn’t allow any duplicates for the primary key field.
By default, Access displays a table’s data in the order of its primary key.
By designating a field such as ContactID as the unique primary key, you can see your data in a meaningful order. In our example, because the ContactID field is an AutoNumber, its value is assigned automatically by Access in the order that a record is put into the system.
Choosing a primary key
Although all of the tables in the Access Auto Auctions application use AutoNumber fields as their primary keys, you should be aware of the reasons why AutoNumbers make such excellent primary keys. The characteristics of primary keys include the following:
The primary key must uniquely identify each record.
The primary key cannot be null.
The primary key must exist when the record is created.
The primary key definition must remain stable—you should never change a primary key value once it is established.
The primary key must be compact and contain as few attributes as possible.
The ideal primary key is, then, a single field that is immutable and guaranteed to be unique within the table. For these reasons, the Access Auto Auctions database uses the AutoNumber field exclusively as the primary key for all tables.
Creating the primary key
The primary key can be created in any of three ways. With a table open in Design View:
Select the field to be used as the primary key and select the Primary Key button (the key icon) in the Tools group in the ribbon’s Design tab.
Right-click on the field to display the shortcut menu and select Primary Key.
Save the table without creating a primary key, and allow Access to automatically create an AutoNumber field.
After you designate the primary key, a key icon appears in the gray selector area to the left of the field’s name to indicate that the primary key has been created.
Creating composite primary keys
Although rarely done these days, it is possible to designate a combination of fields to be used as a table’s primary key. Such keys are often referred to as composite primary keys. As indicated in Figure 34-18, select the fields that you want to include in the composite primary key, then click the key icon in the Tools Ribbon tab. It helps, of course, if the fields lie right next to each other in the table’s design.
Figure 34-18. Creating a composite primary key.
Composite primary keys are primarily used when the developer strongly feels that a primary key should be comprised of data that occurs naturally in the database. There was a time when all developers were taught that every table should have a natural primary key.
The reason that composite primary keys are seldom used these days is because developers have come to realize that data is highly unpredictable. Even if your users promise that a combination of certain fields will never be duplicated in the table, things have a way of turning out differently than planned. Using a surrogate primary key, such as an AutoNumber, separates the table’s design from the table’s data. The problem with natural primary keys (meaning, data that occurs naturally in the table) is that, eventually, given a large enough data set, the values of fields chosen as the table’s primary key are likely to be duplicated.
Furthermore, when using composite keys, maintaining relationships between tables becomes more complicated because the fields must be duplicated in all the tables containing related data. Using composite keys simply adds to the complexity of the database without adding stability, integrity, or other desirable features.