A key is a field that serves a specific function within a table. Several types of keys are important, but for now, I want to focus on the primary key. In the next few chapters, I discuss the primary key at length, and you will spend a great deal of time selecting and using primary keys. But I introduce the concept now for three reasons:
The last objective most concerns me. Early on, new Access users are exposed to primary keys and usually find them confusing. Here's a typical scenario:
You now have a new table with a primary key. But the message doesn't explain much about primary keys. Without some further background, it's almost impossible not to be confused by it.
How Primary Keys Work
One requirement for a relational database is that each record in each table be uniquely identified by a primary key. That means that in a field that is the primary key, there can never be duplicate data.
Let's see what that means in practice. Take a look back at Table 1.1. Each of the fields either currently has a duplicate or has the potential of having a duplicate. For example, in the Last Name field, O'Connell appears twice. You cannot use the Last Name field as a primary key because it's possible that two people will have the same last name. Indeed, even the smallest possibility that a field might have duplicate data disqualifies it as a primary key.
Primary keys can be more than one field. For now, let's keep the discussion simple by assuming a single field for the primary key.
To repeat: When I say that there can't be any duplicates in the primary key, I mean there can be no duplicates. Unless a key is an absolutely unique identifier, it cannot be a primary key.
This obviously sets a very high standard. In everyday life, real uniqueness is highly unusual. Nearly all of us have the same last name as someone else. So finding a field of actual real-life values that describe something that can also be used as a primary key is often difficult.
One solution to the primary key puzzle is to include a field with an AutoNumber type. When you use this data type, each time you add another record, the value in the field increases by 1. Take a look at Table 1.2. It is the same table as Table 1.1, but it includes a Member ID field as a primary key. It has the AutoNumber data type. Each time you add a record, the primary key increases by 1. Each record is thus uniquely identified by the Member ID field because each of its values is different.
Gaps in the Primary Key
Now let's assume that Mr. Maury Hendricks (ID# 3) and Mr. James O'Connell (ID# 8) have dropped out of the club and you have deleted their records (see Table 1.3). You also added a new member, Charlie Moriarty.
If you already had doubts about the usefulness of an AutoNumber field as a primary key, they now intensify. There are gaps in the primary keythere is no record with ID#3 or ID#8. When you added the Moriarty record, its ID wasn't 3it was 11, one more than the last AutoNumber used. The breaks in the primary key order seem inelegant, at best, and simply wrong, at worst. You can't renumber the primary key for all the records without a great deal of work.
But here's what you need to remember: You don't want to close the gaps. You are completely indifferent about whether there are breaks in the order of IDs.
Think of it this way: Say you've just entered some swanky restaurant. You give the hat-check guy your new trench coat and your spouse's mink. He gives you two tags, numbered 7 and 9, for the two coats.
Does it bother you in the least bit that there's no 8? Do you spend the entire meal thinking, "Whoa, the first tag was 7, so why wasn't that next tag an 8? Isn't that suspicious?"
Maybe hangar 8 was broken or had guacamole on it; who knows and who cares? All you're worried about is that your coats are checked and they can be found when you leave. In the same way, your only concern is that Access can find records 7 and 9 when you need to view them.
From another viewpoint, the value 8 in an AutoNumber field doesn't mean the number that's between 7 and 9that is, a number in a sequence that is going to be missed if it's not there. Although the primary key has the AutoNumber data type because the values increase one by one, the numbers in it are being used like text; they simply identify the record. Just as the number on a claim ticket is meaningful only as part of the storage system of the cloak room, a primary key with an AutoNumber data type exists only to serve the storage system of Access. Indeed, if you were going to print the table and give it to a new member, you might well want to exclude the primary key (as you can easily do) precisely because it has no meaning to fellow members.