Add or change a table’s primary key in Access
※ Download: What is a primary key in access
As a consultant and trainer, Adam has spent over 5 years helping government, enterprise, and nonprofit clients use Access and Excel efficiently. ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.
If there are multiple triggers on a single table, they fire in random order, unless there is a dedicated first or last trigger for the table. ProductVendor table form a composite primary key constraint for this table. Cannot be specified for tables that have INSTEAD OF UPDATE triggers.
Add or change a table’s primary key in Access - Each movie is a standalone explanation of a technique or feature. If your table already includes an AutoNumber field, Access uses that field as the primary key.
Access for Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007 A primary key is a field or set of fields with values that are unique throughout a table. Values of the key can be used to refer to entire records, because each record has a different value for the key. Each table can only have one primary key. 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. Select the field or fields that you want to use, and then on the ribbon, click Primary Key. Note: This article is intended for use only with Access desktop databases. Access automatically manages primary keys for new tables in Access web apps and web databases. In this article Overview of primary keys in Access Access uses primary key fields to quickly associate data from multiple tables and combine that data in a meaningful way. You can include the primary key fields in other tables to refer back to the table that is the source of the primary 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 key. In the Orders table it is called a foreign key. A foreign key, simply stated, is another table's primary key. For more information, see. Foreign key If you are moving existing data into a database, you may already have a field that you can use as the primary key. Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key. 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 primary key field, and that it is always unique. What makes a good primary key? An AutoNumber field automatically generates a value for itself when each record is first saved. Therefore, an AutoNumber field meets all three characteristics of a good primary key. For more information on adding an AutoNumber field, see the article. An AutoNumber field makes a good primary key. Examples of poor primary keys Any field that is missing one or more of the characteristics of a good candidate key is a poor choice for a primary key. 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 primary key Reason 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 person may share a ZIP 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 as a separate field. For example, combining the city and an incremented number e. 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. Set the primary key using fields you already have in Access For a primary key to work well, the field must uniquely identify each row, never contain an empty or null value, and rarely ideally, never change. Tip: If you don't see the Navigation Pane, press F11 to display it. To select one field, click the row selector for the field you want. To select more than one field to create a composite key, hold down CTRL and then click the row selector for each field. A key indicator is added to the left of the field or fields that you specify as the primary key. Remove a primary key in Access When you remove the primary key, the field or fields that previously served as primary key will no longer provide the primary means of identifying a record. If you try to remove a primary key that is part of one or more relationships, Access warns you that you must delete the relationships first. You cannot delete a table relationship between open tables. Tip: If you don't see the Navigation Pane, press F11 to display it. If the primary 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 any field in the 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, Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses that field as the primary key.
The primary key consists of one or more columns whose data contained within is used to uniquely identify each row in the table. However, the missing steps are variations on the techniques you've already learned. A collection of fields that describe a person, place, object, event or idea. For more information on adding an AutoNumber field, see the article. This makes sure that every row in the ProductVendor table has a unique combination of ProductID and VendorID. A primary key makes it convenient for a user to add, sort, modify or delete data in a database. Because the primary key is used for lookups and comparisons, a short primary key means the database management system can process it more quickly than a long primary key. Tip: If you don't see the Navigation Pane, press F11 to display it.
