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. Show To set a table’s primary key, open the table in Design view. 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. Although it is possible to override these automated primary keys, we don’t recommend that you do. 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 Database design basics.
1. Primary key 2. 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. When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of "ID" and the AutoNumber data type. A good candidate for a primary key has several characteristics:
If you can’t identify a good key, create an AutoNumber field to use as the 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 Add an AutoNumber field as a primary key.
An AutoNumber field makes a good primary key. 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.
In some cases, you want to use two or more fields in a table as the primary key. 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. 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. To set the primary key:
If you decide to change a table's primary key, you can do so by following these steps: For more information about database design and choosing an appropriate primary key, see the articles: Top of Page
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed InstancePrimary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL Server tables. These are important database objects. Primary Key ConstraintsA table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they are frequently defined on an identity column. When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique. As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite primary key constraint for this table. This makes sure that every row in the ProductVendor table has a unique combination of ProductID and VendorID. This prevents the insertion of duplicate rows.
Foreign Key ConstraintsA foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table. For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. By creating this foreign key relationship, a value for SalesPersonID cannot be inserted into the SalesOrderHeader table if it does not already exist in the SalesPerson table. A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:
Indexes on Foreign Key ConstraintsUnlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index. However, manually creating an index on a foreign key is often useful for the following reasons:
Referential IntegrityAlthough the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table. A foreign key constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data. Cascading Referential IntegrityBy using cascading referential integrity constraints, you can define the actions that the Database Engine takes when a user tries to delete or update a key to which existing foreign keys point. The following cascading actions can be defined. NO ACTION CASCADE SET NULL SET DEFAULT CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. If the Database Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the Database Engine checks for any NO ACTION. Triggers and Cascading Referential ActionsCascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:
Next stepsThe following table lists the common tasks associated with primary key and foreign key constraints. |