Access How To Set Primary Key

A primary key is a field or set of fields with values that are unique throughout a table. Values of the key tin exist used to refer to entire records, considering each record has a different value for the key. Each table can only accept one primary cardinal. Access tin automatically create a primary key field for you when you create a table, or you tin specify the fields that y'all want to utilize as the primary key. This article explains how and why to apply chief keys.

To ready a table's chief cardinal, open the table in Blueprint view. Select the field (or fields) that yous want to use, and so on the ribbon, click Principal Key.

Notation: This article is intended for use just with Access desktop databases. Access automatically manages main keys for new tables in Access web apps and spider web databases. Although it is possible to override these automated primary keys, nosotros don't recommend that you exercise.

In this article

• Overview of primary keys in Admission

• Ready the chief central using fields you already take in Access

• Remove a principal fundamental

• Change the primary key in Access

• Additional information

Overview of primary keys in Access

Access uses primary key fields to rapidly associate data from multiple tables and combine that information in a meaningful manner. You can include the primary key fields in other tables to refer back to the table that is the source of the main key. In those other tables, the fields are chosen 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 tabular array it is called a foreign cardinal. A foreign key, simply stated, is another table's primary key. For more than information, see Database design nuts.

1. Primary key

two. Foreign central

If you are moving existing data into a database, you may already take a field that yous tin use equally the primary key. Often, a unique identification number, such as an ID number or a serial number or code, serves as a main key in a table. For instance, y'all might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary fundamental.

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 main key field, and that information technology is ever unique.

When yous create a new table in Datasheet view, Access automatically creates a primary central for y'all and assigns information technology a field name of "ID" and the AutoNumber information blazon.

What makes a skilful master primal?

A skillful candidate for a primary cardinal has several characteristics:

• It uniquely identifies each row

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

• The values it contains rarely (ideally, never) change

If you can't identify a proficient central, create an AutoNumber field to use as the central. An AutoNumber field automatically generates a value for itself when each record is beginning saved. Therefore, an AutoNumber field meets all three characteristics of a proficient chief primal. For more information on adding an AutoNumber field, run across the article Add an AutoNumber field as a primary key.

An AutoNumber field makes a good master central.

Examples of poor chief keys

Whatever field that is missing i or more of the characteristics of a proficient candidate key is a poor choice for a primary primal. Here are a few examples of fields that would make poor principal keys for a Contacts table, along with reasons why they would exist poor choices.

Poor principal key	Reason
Personal name	Might not be reliably unique, and may alter
Phone number	Likely to modify.
Email accost	Likely to change.
Zip lawmaking	More than 1 person may share a Naught code
Combinations of facts and numbers	The fact portion might change, creating a maintenance burden. Could lead to confusion if the fact portion is repeated equally a separate field. For example, combining the city and an incremented number (east.g., NEWYORK0579) would exist a poor choice if the urban center is likewise stored as a field.
Social Security Numbers	Individual data and not allowed in authorities departments and some organizations. Some people don't have a SSN An individual may have more than i in a lifetime

Composite keys: using multiple fields in combination as a primary key

In some cases, you want to use ii or more fields in a table as the primary key. For example, an Order Details table that stores line items for orders might apply 2 fields in its principal key: Order ID and Product ID. A key that has more one field is chosen a composite key.

Set up the primary central using fields you already take in Access

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

1. Open the database that you want to alter.

2. In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut bill of fare, click Design View.

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

3. Select the field or fields that you want to use as the principal key.

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

   To select more than i field to create a composite fundamental, agree down CTRL then click the row selector for each field.

4. On the Design tab, in the Tools group, click Primary Primal.

   

   A key indicator is added to the left of the field or fields that y'all specify as the primary central.

Remove a primary key in Access

When you remove the primary key, the field or fields that previously served as principal key will no longer provide the primary ways of identifying a record.

Removing the primary key doesn't delete the fields from your table, but it does remove the alphabetize that was created for the primary key.

1. Earlier you lot can remove a chief key, you must make sure that it doesn't participate in any table relationships. If y'all try to remove a master key that is part of ane or more relationships, Access warns y'all that you must delete the relationships first.

   To delete a table relationship, complete the post-obit steps:

   1. If the tables that participate in the tabular array human relationship are open, shut them. Y'all cannot delete a tabular array relationship between open up tables.

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

      

   3. Select Add Tables (Show Table in Access 2022).

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

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

2. After you delete the relationships, in the Navigation Pane, right click the table from which you want to remove the primary key and and so click Design View.

   Tip:If you don't meet the Navigation Pane, printing F11 to display it.

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

   If the principal 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 whatsoever field in the principal key.

4. On the Design tab, in the Tools group, click Primary Central.

   The fundamental 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 yous cull Yes, Access creates an ID field that uses the AutoNumber data blazon to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses that field as the primary key.

Modify the primary key in Access

If you decide to modify a table's master central, you can practise and so by post-obit these steps:

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

2. Ready the primary key using the instructions in the section Fix the primary key.

Boosted information

For more information about database design and choosing an advisable chief key, see the articles:

• Database basics

• Acquire the construction of an Access database

• Create an Access database

• Create a table

Top of Folio

Source: https://support.microsoft.com/en-us/office/add-or-change-a-table-s-primary-key-in-access-07b4a84b-0063-4d56-8b00-65f2975e4379

Share this post