Create or modify a primary key

When a table in your Microsoft Access database has a field or set of fields that uniquely identify each record stored in the table, it can be set as the primary key. The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. This article discusses the purpose of a primary key and also explains how to add, set, change or remove one.

In this article


Overview

Access uses primary key fields to quickly associate data from multiple tables and combine that data in a meaningful way. You can include the primary key fields in other tables to refer back to the table that is the source of the primary key. In those other tables, the fields are called foreign keys. For example, a Customer ID field in the Customers table might also appear in the Orders table. In the Customers table, it is the primary key. In the Orders table it is called a foreign key. A foreign key, simply stated, is another table's primary key. For more information, see Database design basics.

Primary key and foreign key

Callout 1 Primary key
Callout 2 Foreign key

If you are moving existing data into a database, you may already have a field that you can use as the primary key. Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key.

Access automatically creates an index for the primary key, which helps speed up queries and other operations. Access also ensures that every record has a value in the primary key field, and that it is always unique.

When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of "ID" and the AutoNumber data type. The field is hidden by default in Datasheet View, but you can see the field if you switch to Design View.

What makes a good primary key?

A best practice in database design is to use an internally generated primary key. The database management system can generate a unique identifier. For example, you might use the AutoNumber datatype to create a field called CustomerID. The AutoNumber datatype automatically increments the field each time you create a new record. While the number by itself is not of any significance, it provides a great way to reference an individual record in queries. For more information on adding an AutoNumber, see section on Add an AutoNumber primary key.

A good candidate for a primary key has several characteristics:

  • It uniquely identifies each row
  • It is never empty or null — it always contains a value
  • The values it contains rarely (ideally, never) change

 Note    Take care in choosing a primary key because it is difficult to change the primary key in a production table.

Examples of poor primary keys

Any field that is missing one or more of the characteristics of a good candidate key is a poor choice for a primary key. Here are a few examples, along with reasons why they would be poor choices.

Poor primary key Reason
Personal name Might not be reliably unique, and may change
Phone number Likely to change.
E-mail address Likely to change.
Zip code More than one town may share a ZIP code
Combinations of facts and numbers Can be hard to maintain, and could lead to confusion if the fact portion is repeated as a separate field. For example, an account ID comprising the city and an incremented number (e.g., NEWYORK0579) would be a poor choice if the city is also stored as a field.
Social Security Numbers
  • Private information and not allowed in government departments and some organizations.
  • Some people don’t have a SSN
  • An individual may have more than one in a lifetime

 Tip   If a table doesn't have a good candidate for primary key, consider adding a field that has the AutoNumber data type, and using that field as the primary key.

Image showing Products table with primary key field.

Callout 1 A field with the AutoNumber data type often makes a good primary key, because it ensures unique values.

In some cases, you may want to use two or more fields that, together, provide the primary key of a table. For example, an Order Details table that stores line items for orders would use two fields in its primary key: Order ID and Product ID. When a primary key employs more than one field, it is also called a composite key.

Top of Page Top of Page

Use composite keys to set multiple fields in combination as a primary key

In some cases, you want to use two or more fields in a table as the primary key. For example, an Order Details table that stores line items for orders might use two fields in its primary key: Order ID and Product ID. A key that has more than one field is called a composite key. You can create a composite foreign key by using a data-definition query to create a multiple-field constraint. For more information, see the topic Create or modify tables or indexes by using a data-definition query.

Top of Page Top of Page

Add an AutoNumber primary key

 Note   When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it the AutoNumber data type.

  1. Open the database that you want to modify.
  2. In the Navigation Pane, right click the table to which you want to add the primary key and, on the shortcut menu, click Design View.

 Tip   If you don't see the Navigation Pane, press F11 to display it.

  1. Locate the first available empty row in the table design grid.
  2. In the Field Name field, type a name, such as CustomerID.
  3. In the Data Type field, click the drop-down arrow and click AutoNumber.
  4. Under Field Properties, in New Values, click Increment to use incremental numeric values for the primary key, or click Random to use random numbers.

Top of Page Top of Page

Set the primary key

For a primary key to work well, the field must uniquely identify each row, never contain an empty or null value, and rarely (ideally, never) change. To set the primary key:

  1. Open the database that you want to modify.
  2. In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut menu, click Design View.

 Tip   If you don't see the Navigation Pane, press F11 to display it.

  1. Select the field or fields that you want to use as the primary key.

To select one field, click the row selector for the field you want.

To select more than one field, hold down CTRL and then click the row selector for each field.

  1. On the Design tab, in the Tools group, click Primary Key.

Ribbon Design Tab Tools Group

A key indicator is added to the left of the field or fields that you specify as the primary key.

Top of Page Top of Page

Remove the primary key

When you remove the primary key, the field or fields that previously served as primary key will no longer provide the primary means of identifying a record. Removing the primary key only it removes the primary key designation from those fields and does not delete the field or fields from your table.

Removing the primary key also removes the index that was created for the primary key.

  1. Open the database that you want to modify.
  2. Before you can remove a primary key, you must make sure that it doesn't participate in any table relationships. If you try to remove a primary key that is part of one or more relationships, Access warns you that you must delete the relationships first.
To delete a table relationship, complete the following steps:
  1. If the tables that participate in the table relationship are open, close them. You cannot delete a table relationship between open tables.
  2. On the Database Tools tab, in the Relationships group, click Relationships.
  3. If the tables that participate in the table relationship are not visible, on the Design tab, in the Relationships group, click Show Table.
  4. Select the tables to add in the Show Table dialog box, and then click Add, and click Close.
  5. Click the table relationship line for the table relationship that you want to delete (the line becomes bold when it is selected), and then press the DELETE key.
  6. On the Design tab, in the Relationships group, click Close.
  1. After you delete the relationships, in the Navigation Pane, right click the table in which you want to remove the primary key and, on the shortcut menu, click Design View.

 Tip   If you don't see the Navigation Pane, press F11 to display it.

  1. Click the row selector for the current primary key.

If the primary key consists of a single field, click the row selector for that field.

If the primary key consists of multiple fields, click the row selector for any field in the primary key.

  1. On the Design tab, in the Tools group, click Primary Key.

The key indicator is removed from the field or fields that you previously specified as the primary key.

 Note   When you save a new table without setting a primary key, Access prompts you to create one. If you choose Yes, Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses that field as the primary key.

Top of Page Top of Page

Change the primary key

If you decide to change a table's primary key, you can do so by following these steps:

  1. Remove the existing primary key using the instructions in the section Remove the primary key.
  2. Set the primary key using the instructions in the section Set the primary key.

Top of Page Top of Page

Additional information

To make sure that your database design is good try reading the following articles:

Top of Page Top of Page

 
 
Applies to:
Access 2010