Changing the Primary Key

Article 1, “Designing Your Database Application,” on the companion CD discusses the need to have one or more fields that provide a unique value to every row in your table. This field or group of fields with unique values is identified as the primary key. If a table doesn’t have a primary key, you can’t define a relationship between it and other tables, and Access 2007 has to guess how to link tables for you. Even if you define a primary key in your initial design, you might discover later that it doesn’t actually contain unique values. In that case, you might have to define a new field or fields to be the primary key.

Let’s go back to the three tables we built earlier with the Table Analyzer Wizard. Suppose you discover that users are becoming confused by the fact that ContactTypes_ID is a number instead of the actual text. (See our comments about using the Lookup Wizard on page 245.) You could keep the lookup table to help avoid duplicate values, but there’s no reason not to store the actual text value in the Contacts table instead of storing a linking ID.

To fix this, you need to perform the following steps. Be sure to save your work at the end of each step.

1. Open the Contacts table in Design view and insert a new field below ContactTypes_ID named ContactType, data type Text, length 50.

2. Update the new ContactType field with related information from the ContactTypes table. We’ll show you how to do this easily with an update query in Chapter 9, “Modifying Data with Action Queries.” For now, you can switch to Datasheet view and copy what you see in the Lookup to ContactTypes field to your new ContactType field. (There are only 18 rows, so this shouldn’t take you very long.)

3. Open the Relationships window and click the All Relationships button in the Relationships group of the Design tab below Relationship Tools so that you can see the additional relationships that the Table Analyzer Wizard built. Click on the line between Contacts and ContactTypes and press the Delete key to remove the relationship. (You must delete any relationship involving the primary key of a table before you can change the key.) Click Yes to confirm this action.

4. Open the Contacts table in Design view and delete the ContactTypes_ID field.

5. Open the ContactTypes table in Design view and change the primary key from ID to ContactType. (You can also select the ID field and delete it if you like.) Access provides several ways for you to accomplish this task. You could open the Indexes window (as you learned in Chapter 4), delete the primary key definition, and build a new one. A simpler way is to select the new field you want as the primary key and then click the Primary Key button in the Tools group of the Design contextual tab below Table Tools, as shown in Figure 5–40.

   
   Figure 5–40: Select the new field that will become the primary key and then click Primary Key on the Design tab to define the key.

6. Finally, reopen the Relationships window and define a new relationship between ContactType in the ContactTypes table and your new ContactType field in the Contacts table.

Keep in mind that you can directly change the primary key for any table that does not have any relationships defined. Also, when the table contains data, the new fields that you choose for a primary key must have unique values in all the rows.