Setting the Primary Key


10 Minute Guide to Microsoft Access 2002
By Joe Habraken
Table of Contents
Lesson 5.  Creating a Table from Scratch

Setting the Primary Key

An important aspect of table structure design is that each table must have a field that is used to uniquely identify the records in the table. This field is called the primary key. Setting an appropriate key is trickier than it appears because no two records can have the same key value. In a table of customers, for example, you might think the Last Name field would be a good key, but this theory falls flat as soon as you find that you have more than one customer with the same last name . A more appropriate primary key for your customers is a Social Security number (although people don't like to give these out) because it uniquely identifies each customer.

A good general rule is to create an identification field, such as a customer number, that allows you to assign a sequential number to each customer as you add them to your database table. Access can even help you out with the assigning of numbers to the customers because you can make the field type for the Customer Number field AutoNumber. An AutoNumber field type assigns a number to each record starting with the number 1.


Creating the Primary Key Typically, the first field in the table serves as the primary key.

To set a primary key, follow these steps:

  1. In Table Design view, select the field that you want for the primary key.

  2. Select Edit, Primary Key , or click the Primary Key button on the toolbar. A key symbol appears to the left of the field name, as shown in Figure 5.3.

    Figure 5.3. The primary key field is marked by a key symbol.

  3. After you select the primary key and have finished entering your table fields, you should save the table. Click the Save button on the Table Design toolbar to open the Save As dialog box.

  4. Enter a name for the table, and then click OK .

  5. After saving the table, you can either switch to the Datasheet view (to enter data) by clicking the View button on the toolbar, or you can choose to close the table by clicking the table's Close (X) button.

Plain English

No Primary Key! If you attempt to close your new table in the Design view without specifying a primary key (even if you have saved the table), a message appears, letting you know that no primary key has been assigned. Click Yes on the message box to have Access assign a primary key to the table. If you have set up your table to contain an AutoNumber field, Access will make this field the primary key. Otherwise, Access creates a new AutoNumber field in the table and specifies it as the primary key. You can change the name of this new field as needed.


10 Minute Guide to Microsoft Access 2002
10 Minute Guide to Microsoft Access 2002
ISBN: 0789726319
EAN: 2147483647
Year: 2000
Pages: 160
Authors: Joe Habraken © 2008-2017.
If you may any questions please contact us: