Home > Articles > Data > Access

  • Print
  • + Share This

Setting the Primary Key for Your Table

The final step is to set the primary key for your table. The best way to think about a key is as one or more fields in a record whose value(s) uniquely identifies that record. For example, pretend you're a football coach and you've created a table to hold player information that has fields such as last name, first name, jersey number, height, weight, and position played. In this example, the jersey number would be used as the primary key, since it uniquely identifies a particular player. Any other field can refer to more than one player; for example, there may be several players on the roster with a last name of Smith or whose height is 6'. For best performance, you should always set a primary key for your table.

For the guest book table, the field that uniquely defines a record is email. Any other field can refer to more than one record; for example, there maybe be several users in the guest book whose age is 15. Thus, the email field will be used as the primary key for our guest book table. However, before we set the primary key for our guest book table, let's look at what happens if you don't set a primary key:

  1. Try closing the table design window by clicking the Close (X) button in the upper-right corner of the window. The dialog box shown in Figure 19 pops up to ask whether you want to save your design.

  2. Figure 19 This dialog box asks whether you want to save your table design.

  3. Click Yes. Another dialog box pops up, asking for your table's name.

  4. Enter GuestBook (no spaces between the words) as your table's name (see Figure 20).

  5. Figure 20 This dialog box asks for the table's name.

  6. Click OK. Access displays a message to warn you that you haven't defined a primary key (see Figure 21).

  7. Figure 21 Your table doesn't have a primary key.

  8. Don't click Yes! If you click Yes, Access will automatically add an extra field named ID to your table (Data Type: Auto Number) and make that field the primary key. From a design standpoint, it's always preferable to use an existing field as the primary key. Thus, we need to go back and create a primary key, so click Cancel instead. The dialog box disappears and the table design reopens (see Figure 22).

  9. Highlight the email field by clicking the gray rectangular button to the left of the field name.

  10. On the Table Design toolbar, locate the Primary Key button (it looks like a key, as shown in Figure 23). Click the Primary Key button.

  11. Figure 22 The GuestBook table with all fields added.

    Figure 23 The primary key button.

  12. To the left of the email field name, in the middle of the gray rectangular button, you should now see an outline of a key, indicating that email is the primary key for your table (see Figure 24).

  13. Figure 24 The email field is the primary key.

  14. Now you really are finished with the GuestBook table design! Click the Close (X) button in the upper-right corner of the table design window.

  15. A dialog box asks whether you want to save your table's design (refer to Figure 19). Click Yes.

  16. When asked for your table's name (refer to Figure 20), enter GuestBook (no spaces between the words). Although we entered this as the table's name in an earlier step, remember that we canceled out that operation, so now we need to enter the table's name again.

  17. Click OK.

Access redisplays the start window. Notice that the GuestBook table now appears as an icon in the start window (see Figure 25).

Figure 25 The start window with an icon for the GuestBook table.

If you don't see the GuestBook table icon, it's probably because the Tables object isn't selected. Although you really shouldn't change the design of your table at this point, there are times when you may need to add a new field, delete an existing field, or change the name of a field. You can do these operations and more by right-clicking the table icon (such as GuestBook) and selecting Design View from the pop-up menu, or by clicking the table icon and clicking the Design icon in the start window's toolbar (not shown).

We can now start populating our GuestBook table with data.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.