Section 2.4. The Primary Key | Access 2007[c] The Missing Manual

2.4. The Primary Key

Design view also allows you to set a table's primary key , which is a field (or a combination of fields) that's unique for each record. Every table must have a primary key. To understand why the primary key's important, you need to consider a little bit more about how databases work. The box "How Access Prevents Duplicate Records" in Section 2.4.1 has the full story.

Choosing a primary key is trickier than it seems. Imagine you have a list of friends (and their contact information) in a table named People. You may logically assume that you can create a primary key using a combination of first and last name .

UP TO SPEED
How Access Prevents Duplicate Records

In order to function correctly, a database program like Access needs to be able to tell the difference between each and every record in your table. In other words, you can't insert two records with exactly the same information. Databases are notoriously fussy, and they don't tolerate this sort of sloppiness.

The challenge of preventing duplicates isn't as easy as it seems. Access is designed to be blisteringly fast, and it can't afford to double-check your new record against every other record in the table to see if there's a duplicate. So instead, it relies on a primary key . As long as every record in a table has a unique, never- duplicated primary key, you can't have two identical records. (At worst, they'll be two almost identical records that have the same information in all their other fields, but have different primary keys. And this is perfectly acceptable to Access.)

In an Employees table, the Social Security number could serve as the primary key. This method works well, because when you insert a new record, Access can check for duplicates by breezing through the list of Social Security numbers , which is much faster than scanning through the entire table.

Unfortunately, that just won't doafter all, many are the address books that have two Sean Smiths.

Your best solution's to invent a new piece of information. You can label every individual in your contact list with a unique ID number. Best of all, you can get Access to automatically create this number for you (and make sure that no two people get the same number), so you don't even need to think about it. That way, if you have two Sean Smiths, each one has a different ID. And even if Ferris Wheel Simpson decides to change his first name, the ID remains the same.

This approach is exactly the one Access uses when you create a table using the Datasheet view. Consider the Dolls table you built in Chapter 1. You'll notice that it includes a field named ID, which Access fills automatically. You can't set the ID value in a new record, or change it in an existing record. Instead, Access takes complete control, making sure each bobblehead has a different ID number. This behavior's almost always what you want, so don't try to change it or delete the ID field.

However, there's one exception. If you create a table in Design view by choosing Create Tables Table Design, then Access assumes you know what youre doing, and it doesn't create an ID field for you. You need to add an ID field (or something like it).

2.4.1. Creating Your Own Primary Key Field

If your database doesn't have an ID field (perhaps because you created it using the Create Tables Table Design command), its up to you to create one and set the primary key. Here's how to do it:

Create a new field by typing a name in the Field Name column .

For automatically generated values, the name ID is the best choice. Some people prefer to be a little more descriptive (for example, BobbleheadID, CustomerID, and so on), but it's unnecessary.
In the Data Type column, choose AutoNumber .

By choosing the AutoNumber data type, you make sure that Access generate a unique ID value for every new record you insert. If you don't want this process to happen, you can choose something else (like the Text or Number data type). You'll be responsible for entering your own unique value for each record, which is more work that it seems.
Right-click the field, and then choose Primary Key .

This choice designates the field as the primary key for the table. Access doesn't allow duplicate values in this field.

Note: If you want to make a primary key that includes more than one field, then you need to take a slightly different approach. First, click the margin next to the field name, and then drag the mouse to select more than one field. Then, hold down Shift, and then right-click your selection. Now you can choose Primary Key.

UP TO SPEED
Why It's Important to Be Unique

You won't completely understand why it's so important for each record to have a unique ID number until you work with the more advanced examples in later chapters. However, one of the reasons is that other programs that use your database need to identify a record unambiguously .

To understand why there's a problem, imagine that you've built a program for editing the Dolls table. This program starts by retrieving a list of all your table's bobbleheads. It displays this list to the person using the program, and lets her make changes. Here's the catchif a change is made, the program needs to be able to apply the change to the corresponding record in the database. And in order to apply the change, it needs some unique piece of information that it can use to locate the record. If you've followed the best design practices described above, the unique "locator" is the bobblehead's ID.