Access 2007 Primary Key

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.

Primary key 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.

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

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.

• 1. Type or paste the following SQL into the query:

CREATE INDEX ON ( ) WITH PRIMARY

• 1. 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.

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

  2. 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.

  3. 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.

• 1. Type or paste the following SQL into the query:

DROP INDEX ON

• 1. 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.

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

  2. Press CTRL+S to save the query.