Create or remove a primary key Every table in your database should have a primary key — a field or set of fields with...

Create or remove a primary key

Every table in your database should have a primary key — a field or set of fields with a unique value for each record stored in the table. You can use the primary key to identify and refer to each record.

Access can automatically create a primary key field for you when you create a table, or you can specify the fields that you want to use as the primary key. This article explains how and why to use primary keys.

One of the reasons to create a primary key is to use it to create table relationships. This topic does not explain how to create relationships. For more information, see the See Also section.

In this article

Overview

Add an AutoNumber primary key

Specify fields to use as the primary key

Remove a primary key

Use SQL to create or remove a primary key

For more information

Overview

When you design a database, you divide your information into tables in such a way that each table is about one subject, such as Customers or Orders. This helps prevent redundancy and contradictory data.

For example, each customer might have many orders. Rather than store the customer's address in each record in the Orders table, you store that information once in a record in the Customers table. To combine the customer information with the rest of the information about the order, you create a relationship between the two tables, by using key fields.

Primary keys

A table can only have one primary key. A primary key is a field or set of fields that:

• Has a unique value for each record

• Is indexed

• Identifies the record

You can add the primary key from one table to another table to create a relationship between them. In the other table, it is called a foreign key.

For example, suppose you use the primary key of the Customers table in the Orders table. In the Orders table it is a foreign key.

1. Primary key

2. Foreign key

Foreign keys

A foreign key, simply stated, is another table's primary key. The values in a foreign key field match values in the primary key, indicating that the two records are related — for example, a customer and an order that she has placed. Unlike primary keys:

• A table can have more than one foreign key.

• A foreign key does not necessarily have unique values.

• A foreign key cannot reliably identify a particular record. For example, you cannot always tell which record you are viewing from the Orders table by looking at the Customer ID.

You create a foreign key when you use the Lookup Wizard to create a field.

Candidate keys

Sometimes a table already contains a good primary key field, such as a serial number or a product code. Such fields are called candidate keys.

For example, if you track post-secondary educational outcomes, you might have a Colleges table. Suppose there is an official database of colleges that provides a code number for each college, such as the U.S. Department of Education Integrated Postsecondary Education Data System (IPEDS). You could use the code that system provides as the primary key for your Colleges table.

A good candidate key has the following characteristics:

• Each value is unique. No two records in the table have the same value for the key.

• It is never empty or null — it always contains a value.

• Once established, each value is relatively permanent. Ideally, the values in the key field never change — they only get added or removed.

An example of a poor choice for a primary key is a customer's name. Although some names are unique, some are not. Because you cannot be sure that values will be unique, names are not candidate keys.

When you create a new table in Datasheet view, Access automatically creates a primary key field for you, names the field ID, and gives it 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.

AutoNumber fields

If your table doesn't have any good candidate keys and you didn't create the table in Datasheet view, consider adding a field that has the AutoNumber data type, and then using that field for the primary key. An AutoNumber field automatically gets a new, unique value for each record that is added. Even if you delete records, the values are not reused. An AutoNumber therefore produces unique values for each record, and makes a good primary key.

1. A column with the AutoNumber data type often makes a good primary key, because it ensures that no two Product IDs are the same.

Composite keys

In some cases, you want to use two or more fields in combination as the primary key for a table. 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.

Note: You cannot create a composite foreign key by using the Lookup Wizard. 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

Add an AutoNumber primary key

1. In the Navigation Pane, right-click the table to which you want to add a primary key and then click Design View on the shortcut menu.

2. In the first empty row of the table design grid, in the Field Name column, type a name, such as CustomerID.

3. In the Data Type column, click the drop-down arrow and then 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.

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

   

   A key indicator appears to the left of the AutoNumber field.

   Press CTRL+S to save your table design changes.

Top of Page

Specify fields to use as the primary key

1. In the Navigation Pane, right-click the table for which you want to set the primary key and then click Design View on the shortcut menu.

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

   Tip: To select more than one field, hold down CTRL while you click the fields.

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

   

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

   Press CTRL+S to save your table design changes.

Top of Page

Remove a primary key

When you remove a table's 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 does not delete the field or fields from your table, however. Rather, it removes the primary key designation from those fields.

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

1. 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 for which a relationship exist, Access warns you that you must delete the relationship first.

   Delete a table relationship

   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 Show/Hide 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. Then select the tables to add in the Show Table dialog box, click Add, and then click Close.

   4. Click the table relationship line for the table relationship that you want to delete (the line appears bold when it is selected), and then press the DELETE key.

   5. On the Design tab, in the Relationships group, click Close.

      

2. In the Navigation Pane, right-click the table from which you want to remove the primary key and then click Design View on the shortcut menu.

3. Select the current primary key field.

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

4. 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, an ID field is created that uses the AutoNumber data type to provide a unique value for each record. If your table already includes a AutoNumber field, Access uses that field as the primary key.

Top of Page

Change a primary key

To change a table's primary key, follow these steps:

1. Remove the existing primary key using the instructions in the section Remove a primary key.

2. Create a new primary key using the instructions in the section Specify fields to use as the primary key.

Top of Page

Use SQL to create or remove a primary key

In Access, a primary key is implemented as an index. When you use Design view to create a primary key, Access creates the index for you. If you prefer, you can use a data-definition query to create a primary key. You can reuse the query whenever you want to create the same primary key. This can be handy if you peridoically drop and recreate a table.

Create a primary key by using SQL

To create a primary key for an existing table, you use a CREATE INDEX command.

You use the CREATE INDEX command in a data-definition query.

1. On the Create tab, in the Other group, click Query Design.

2. Close the Show Table dialog box.

3. On the Design tab, in the Query Type group, click Data Definition.

   The query switches to SQL view.

4. Type or paste the following SQL into the query:

   CREATE INDEX 
    ON  ( )
    WITH PRIMARY
   

5. At the end of the first line, type a name for the index.

   Tip: Consider starting the name with the letters pk to indicate that the index is a primary key. Naming your primary key indexes consistently will make it easier to tell them apart from other indexes.

6. In the second line, after the ON keyword but before the parentheses, type the name of the table.

7. Inside the parentheses, type the fields that you want to use as the primary key. If you type more than one field, separate the fields by using commas.

8. Press CTRL+S to save the query.

   Note: You cannot create an index that already exists. If you want to reuse a query that creates a primary key, you must first remove the existing primary key.

Remove a primary key by using SQL

To remove a primary key from a table, you use a DROP INDEX command.

You use the DROP INDEX command in a data-definition query.

1. On the Create tab, in the Other group, click Query Design.

2. Close the Show Table dialog box.

3. On the Design tab, in the Query Type group, click Data Definition.

   The query switches to SQL view.

4. Type or paste the following SQL into the query:

   DROP INDEX 
    ON  
   

5. At the end of the first line, type the name of the index.

   Tip: If you don't know the name of the index, you can find out. First, open the table in Design view. Then, on the Design tab, in the Show/Hide group, click Indexes.

6. In the second line, after the ON keyword but before the parentheses, type the name of the table.

7. Press CTRL+S to save the query.

For more information

For more information about database design and choosing an appropriate primary key, see the articles Database design basics and Create a table.

Top of Page