Primary Keys

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:

• It exposes you to fields with the AutoNumber data type, thus rounding out our discussion of data types.

• It hints at what distinguishes Access tables from other tables you've worked with.

• I want to make sure that you don't get hung up on the primary key concept.

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:

1.	You begin to create a table in a database.
2.	You begin to enter fields and select data types in Design view (see Figure 1.1). Figure 1.1. A table in Design view. The Data Type drop-down menu displays the various data types. (The last selection, Lookup Wizard, is not a data type and is discussed in Chapter 5.) As you enter fields, you also likely choose field properties and provide descriptions. These topics are covered in Chapter 5.
3.	After you've created the table, you attempt to save it. Access issues a message that says you have not defined a primary key. It also says that, although a primary key isn't required, it's "highly recommended" (see Figure 1.2). Figure 1.2. Access recommends that you define a primary key.
4.	You click Yes and Access creates a table with a primary key with the AutoNumber data type.

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.

NOTE

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.

Table 1.2. The Table of Contact Info Now Includes a Primary Key

Member ID	Last Name	First Name	Address	Phone
1	Michaelson	Helen	659 Kareem Abdul-Jabbar Ave.	555-6548
2	Harrelson	George	42 Karl Malone Dr.	555-9834
3	Hendricks	Maury	1178 Michael Jordan Cir.	555-9846
4	Isaacs	Heather	462 Wilt Chamberlain Ctr.	555-7666
5	Crandall	Orlando	378 Moses Malone St.	555-1209
6	O'Connell	William	463 Elvin Hayes Ave.	555-8736
7	Benton	Elizabeth	355 Oscar Robertson Way	555-0234
8	O'Connell	James	536 Dominique Wilkins St.	555-9065
9	Bradley	Milton	1111 Hakeem Olajuwon Towers	555-0036
10	Ordell	Sally	125 John Havlicek Ave.	555-4609

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.

Table 1.3. Table of Contact Info with Deleted Records

Member ID	Last Name	First Name	Address	Phone
1	Michaelson	Helen	659 Kareem Abdul-Jabbar Ave.	555-6548
2	Harrelson	George	42 Karl Malone Dr.	555-9834
4	Isaacs	Heather	462 Wilt Chamberlain Ctr.	555-7666
5	Crandall	Orlando	378 Moses Malone St.	555-1209
6	O'Connell	William	463 Elvin Hayes Ave.	555-8736
7	Benton	Elizabeth	355 Oscar Robertson Way	555-0234
9	Bradley	Milton	1111 Hakeem Olajuwon Towers	555-0036
10	Ordell	Sally	125 John Havlicek Ave.	555-4609
11	Moriarty	Charlie	522 Walt Frazier Turnpike	555-4889

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.