Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
A primary key is a field or set of fields with unique values across a table. Values of the key can be handled to allude to complete records, because each record has a distinct value for the key. Every table can simply have one primary key. Access can intuitively formulate a primary key field for you once you design a table, or you can elaborate your fields to use as the primary key. This article explains how and why to use primary keys.
To format a table’s primary key, access the table in Design view. Choose your desired field (or fields) to use, and then on the ribbon, press Primary Key.
Note: This article is intended for use only with Access desktop databases. Access automatically manages primary keys for new tables in Access web apps and web databases. Although it is possible to override these automated primary keys, we don’t recommend that you do.
In this article
Overview of primary keys in Access
Access uses primary key fields to swiftly link data from several tables and integrate that data in a purposeful way. You can add the primary key fields in other tables to focus back on that table that is the primary key’s source. In those other tables, the fields are named 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.
1. Primary key
2. Foreign key
If you are relocating current data into a database, you might previously have a useable field as the primary key. Regularly, a unique identification number, such as an ID number or a serial number or code, occupies itself 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 promptly assigns an index for the primary key, which hastens queries and other operations. Access even guarantees that each record has a unique value in the primary key field.
After you produce a new table in Datasheet view, Access autonomously manufactures a primary key for you and assigns it a field name of “ID” and the AutoNumber data type.
What makes a good primary key?
An effective candidate for a primary key has numerous characteristics:
- It uniquely classifies each row;
- It always includes a value (excluding null or empty);
- The values it has rarely (ideally, never) update.
If you’re unable to identify a good key, design an AutoNumber field to use as the key. An AutoNumber field promptly materialises a value independently once every record is initially saved. Therefore, an AutoNumber field satisfies all three characteristics of a good primary key. For more information on adding an AutoNumber field, check out the article Add an AutoNumber field as a primary key.
An AutoNumber field makes a good primary key.
Examples of poor primary keys
Any field that is without one or more of the characteristics of a good candidate key is a poor choice for a primary key. Here are a few examples of fields that would make poor primary keys for a Contacts table, along with reasons why they would be poor choices.
|Poor primary key||Reason|
|Personal name||May not be reliably unique, and may differ|
|Phone number||Prone to change.|
|E-mail address||Prone to change.|
|Postcode/Zip code||Multiple people may share a postcode/ZIP code.|
|Combinations of facts and numbers||The fact portion may evolve, causing a maintenance hiccup. Could result in confusion if the fact portion is recurred as a separate field. For example, combining 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 forbidden in government departments and some organisations. Some people don’t have a SSN. An individual may acquire several instead of one over in a lifetime.|
Composite keys: using multiple fields in combination as a primary key
In some scenarios, you want to rely on 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.
Set the primary key using fields you already have in Access
For a primary key to work effectively, the field must uniquely identify each row, never include an empty or null value, and rarely (ideally, never) vary. To set the primary key:
- Enter the database that you want to modify.
- In the Navigation Pane, right-click your target table for setting the primary key and, on the shortcut menu, choose Design View.
Tip: If you don’t see the Navigation Pane, press F11 to display it.
- Pick your right field or fields to use as the primary key. To choose one field, press the row selector for the field you want. To confirm multiple fields to assemble a composite key, hold down CTRL and then select the row selector for each field.
- On the Design tab, in the Tools group, pick Primary Key.
A key indicator is inserted to the left of the field or fields that you specify as the primary key.
Remove a primary key in Access
After you erase the primary key, the field or fields that formerly acted as primary key will seldom offer the main means of identifying a record.
Deleting the primary key leaves the fields from your table, although it does erase the index that was developed for the primary key.
- Before you can delete a primary key, you have to review that it has no interaction in any table relationships. If you attempt to erase a primary key that is integral to one or more relationships, Access alerts you that you need to delete the relationships first.
To delete a table relationship, complete the following steps:
a. If the tables that engage in the table relationship are open, exit them. You cannot delete a table relationship between open tables.
b. On the Database Tools tab, in the Relationships group, pick Relationships.
c. If the tables that participate in the table relationship are hidden, on the Design tab, in the Relationships group, select Show Table.
d. Specify the tables to insert in the Show Table dialogue box, and then press Add, and pick Close.
e. Select the table relationship line for your intended table relationshipto delete (the line changes to bold once picked), and then press the DELETE key.
f. On the Design tab, in the Relationships group, press Close.
- Following you erasing the relationships, in the Navigation Pane, right-click your preferred table to remove the primary key and then select Design View.
Tip: If you don’t see the Navigation Pane, press F11 to display it.
- Pick the row selector for the existing primary key. If the primary key contains a single field, choose the row selector for that field. If the primary key comprises several fields, select the row selector for any field in the primary key.
- On the Design tab, in the Tools group, pick Primary Key. The key indicator is deleted from the field or fields that you previously specified as the primary key.
Note: Once you save a new table without creating a primary key, Access urges you to make one. If you confirm Yes, Access produces an ID field that uses the AutoNumber data type to offer a unique value for each record. If your table is previously equipped with an AutoNumber field, Access designates that field as the primary key.
Change the primary key in Access
If you reconsider your table’s primary key, you can do so by following these steps:
- Erase the current primary key using the instructions in the section Remove the primary key.
- Format the primary key using the instructions in the section Set the primary key.
For greater information about database design and selecting a compatible primary key, see the articles: