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 . Unfortunately, that just won't doafter all, some address books 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:
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.