Access for 365 accessibility 2021 access 2019 accessibility 2016 access 2013 accessibility 2010 accessibility 2007 More...Less

A primary vital is a field or collection of areas with worths that are unique throughout a table. Worths of the vital can be used to express to whole records, since each record has actually a different value because that the key. Every table can only have one major key. Access can immediately create a primary key field for you once you produce a table, or you have the right to specify the fields that you desire to use as the main key. This short article explains how and also why come use main keys.

You are watching: How to set primary key in access

To set a table’s primary key, open the table in architecture view. Choose the field (or fields) that you want to use, and also then on the ribbon, click Primary Key.

Note:  This post is intended because that use just with Access desktop computer databases. Access automatically manages primary keys for brand-new tables in access web apps and also web databases. Although it is possible to override these automated main keys, us don’t recommend that you do.

In this article

Overview of primary secrets in Access

Access provides primary an essential fields to quickly associate data from multiple tables and also combine the data in a meaningful way. Friend can include the primary key fields in various other tables to refer earlier to the table the is the resource of the main key. In those various other tables, the fields are dubbed foreign keys. Because that example, a client ID field in the customers table might likewise appear in the orders table. In the client table, the is the main key. In the order table it is called a international key. A international key, just stated, is an additional table"s main key. For more information, view Database design basics.


1. Primary key

2. International key

If you are moving existing data into a database, girlfriend may currently have a ar that you have the right to use together the primary key. Often, a distinct identification number, such together an i would number or a serial number or code, serves as a primary an essential in a table. For example, you could have a client table whereby each customer has actually a distinctive customer identifier number. The customer ID field is the main key.

Access instantly creates one index for the major key, i m sorry helps speed up queries and other operations. Access also ensures the every record has actually a value in the primary key field, and also that that is constantly unique.

When you produce a new table in Datasheet view, accessibility automatically creates a primary an essential for you and also assigns it a ar name that "ID" and also the AutoNumber data type.

What makes a good primary key?

A good candidate for a primary an essential has several characteristics:

It unique identifies each row

It is never ever empty or null — it constantly contains a value

The values it contains rarely (ideally, never) change

If friend can’t determine a great key, produce an AutoNumber field to usage as the key. One AutoNumber field automatically generates a value for itself once each record is an initial saved. Therefore, an AutoNumber field meets all three characteristics of a good primary key. For an ext information on adding an AutoNumber field, see the article add an AutoNumber field as a major key.


An AutoNumber ar makes a great primary key.

Examples of negative primary keys

Any field that is lacking one or an ext of the characteristics of a great candidate vital is a poor selection for a main key. Right here are a couple of examples of fields that would certainly make bad primary tricks for a contact table, together with reasons why they would be bad choices.

Poor main key


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 human being may share a ZIP code

Combinations the facts and numbers

The fact section might change, developing a maintain burden. Could lead to man if the fact part is repetitive as a separate field. Because that example, combining the city and an incremented number (e.g., NEWYORK0579) would certainly be a poor an option if the city is likewise stored together a field.

Social security Numbers

Private information and not allowed in federal government departments and some organizations.

Some world don’t have actually a SSN

An individual may have an ext than one in a lifetime

Composite keys: using multiple fields in combination as a primary an essential

In some cases, you desire to use two or an ext fields in a table as the main key. For example, an stimulate Details table that stores line items for orders can use two areas in its primary key: order ID and also Product ID. A crucial that has an ext than one field is referred to as a composite key.

Set the primary vital using fields you already have in Access

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

Open the database that you desire to modify.

In the navigation Pane, ideal click the table in i beg your pardon you want to collection the primary key and, top top the shortcut menu, click Design View.

Select the field or areas that you want to usage as the major key.

To pick one field, click the heat selector for the ar you want.

To select much more than one field to create a composite key, organize down CTRL and also then click the heat selector because that each field.

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


A an essential indicator is added to the left the the ar or areas that girlfriend specify together the main key.

Remove a primary vital in Access

When you remove the major key, the field or fields that previously served as primary vital will no longer carry out the primary way of identifying a record.

Removing the primary crucial doesn’t delete the fields from her table, however it does remove the index that was produced for the main key.

Before you deserve to remove a main key, you must make certain that it doesn"t get involved in any kind of table relationships. If you shot to eliminate a primary crucial that is component of one or an ext relationships, access warns you the you have to delete the relationships first.To delete a table relationship, finish the complying with steps:

If the tables that participate in the table connection are open, near them. You cannot delete a table relationship in between open tables.

On the Database Tools tab, in the Relationships group, click Relationships.


Select Add Tables (Show Table in access 2013).

Click the table relationship line because that the table relationship that you desire to delete (the line becomes bold when it is selected), and then push the DELETE key.

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

After girlfriend delete the relationships, in the navigating Pane, ideal click the table from which you want to eliminate the primary an essential and climate click Design View.

Click the row selector for the existing primary key.

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

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

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

The vital indicator is eliminated from the ar or areas that you formerly specified as the major key.

See more: How Long Does It Take To Watch All The Harry Potter Movies, A Spellbinding Marathon!

Note: When you save a brand-new table without setting a primary key, accessibility prompts friend to develop one. If you select Yes, access creates an ID ar that supplies the AutoNumber data kind to carry out a distinct value for each record. If her table currently includes an AutoNumber field, access uses that field as the main key.

Change the primary vital in Access

If you decide to adjust a table"s main key, you deserve to do so by following these steps:

Additional information

For more information around database design and choosing an ideal primary key, watch the articles: