Can You Have More Than One Primary Key

A primary key is a field or ready of fields with values that are unique throughout a table. Values of the key can exist used to refer to entire records, considering each record has a different value for the key. Each table can only take one principal fundamental. Admission can automatically create a primary key field for you when you lot create a table, or you lot can specify the fields that you want to utilise as the principal key. This article explains how and why to use chief keys.

To set a table's primary key, open the table in Blueprint view. Select the field (or fields) that yous want to utilize, and so on the ribbon, click Primary Key.

Note: This article is intended for utilize only with Access desktop databases. Access automatically manages primary keys for new tables in Admission web apps and web databases. Although information technology is possible to override these automated primary keys, we don't recommend that y'all do.

In this article

• Overview of primary keys in Access

• Set the primary primal using fields you already have in Access

• Remove a principal key

• Change the master key in Access

• Boosted information

Overview of primary keys in Admission

Access uses primary fundamental fields to quickly associate data from multiple tables and combine that data in a meaningful way. You can include the primary cardinal fields in other tables to refer back to the table that is the source of the master key. In those other tables, the fields are called 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 primal. In the Orders table it is called a foreign key. A foreign key, but stated, is another table's primary key. For more data, see Database design basics.

i. Primary key

2. Foreign key

If you are moving existing information into a database, yous may already have a field that y'all can employ every bit the main cardinal. Often, a unique identification number, such as an ID number or a series number or lawmaking, serves as a primary key in a table. For example, you might accept a Customers table where each customer has a unique customer ID number. The customer ID field is the master key.

Access automatically creates an index for the primary fundamental, which helps speed upwardly queries and other operations. Access also ensures that every tape has a value in the primary primal field, and that information technology is always unique.

When yous create a new tabular array in Datasheet view, Admission automatically creates a primary fundamental for you lot and assigns it a field name of "ID" and the AutoNumber data type.

What makes a practiced primary key?

A practiced candidate for a primary fundamental has several characteristics:

• Information technology uniquely identifies each row

• Information technology is never empty or cypher — it always contains a value

• The values information technology contains rarely (ideally, never) modify

If you tin't place a good key, create an AutoNumber field to employ as the key. An AutoNumber field automatically generates a value for itself when each tape is kickoff saved. Therefore, an AutoNumber field meets all three characteristics of a practiced primary key. For more information on adding an AutoNumber field, meet the article Add together an AutoNumber field as a primary fundamental.

An AutoNumber field makes a good principal primal.

Examples of poor primary keys

Whatever field that is missing one or more of the characteristics of a adept candidate key is a poor choice for a primary fundamental. 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 principal central	Reason
Personal name	Might not exist reliably unique, and may modify
Phone number	Likely to change.
E-mail address	Likely to change.
Zip lawmaking	More than one person may share a Cipher code
Combinations of facts and numbers	The fact portion might change, creating a maintenance burden. Could atomic number 82 to confusion if the fact portion is repeated as a dissever field. For example, combining the urban center and an incremented number (e.g., NEWYORK0579) would exist a poor choice if the city is also stored as a field.
Social Security Numbers	Individual data and non allowed in government departments and some organizations. Some people don't have a SSN An private may have more than than ane in a lifetime

Composite keys: using multiple fields in combination every bit a primary key

In some cases, you want to use two or more fields in a table as the primary fundamental. For case, an Order Details tabular array that stores line items for orders might use 2 fields in its primary key: Order ID and Product ID. A primal that has more ane field is called a composite cardinal.

Set the master key using fields you already have in Admission

For a main key to work well, the field must uniquely place each row, never incorporate an empty or zilch value, and rarely (ideally, never) change. To gear up the primary key:

1. Open the database that you want to modify.

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

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

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

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

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

4. On the Pattern tab, in the Tools group, click Primary Key.

   

   A cardinal indicator is added to the left of the field or fields that you specify equally the master key.

Remove a main primal in Access

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

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

1. Before yous can remove a primary key, you lot must make sure that it doesn't participate in whatever table relationships. If y'all endeavor to remove a main key that is office of one or more relationships, Access warns you that yous must delete the relationships starting time.

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

   1. If the tables that participate in the table relationship are open up, close them. You lot cannot delete a tabular array human relationship between open tables.

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

      

   3. Select Add Tables (Show Table in Admission 2013).

   4. Click the tabular array relationship line for the table relationship that y'all want to delete (the line becomes bold when it is selected), and so press the DELETE key.

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

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

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

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

   If the master primal 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 any field in the primary key.

4. On the Blueprint tab, in the Tools group, click Chief Key.

   The key indicator is removed from the field or fields that you previously specified as the primary central.

Notation:When yous save a new table without setting a chief key, Access prompts yous to create i. If y'all choose Yes, Access creates an ID field that uses the AutoNumber information blazon to provide a unique value for each record. If your table already includes an AutoNumber field, Admission uses that field as the primary central.

Change the primary key in Access

If you make up one's mind to change a table'due south master central, y'all can exercise then past following these steps:

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

2. Fix the chief primal using the instructions in the department Ready the main key.

Boosted information

For more than information nigh database pattern and choosing an appropriate main central, run across the articles:

• Database basics

• Learn the construction of an Access database

• Create an Access database

• Create a tabular array

Top of Page

wardoribirlead.blogspot.com

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