Primary and Foreign Key Constraints

  • Article
  • ix minutes to read

Applies to: yesSQL Server 2022 (13.ten) and later YesAzure SQL Database YesAzure SQL Managed Instance

Primary keys and foreign keys are 2 types of constraints that tin be used to enforce data integrity in SQL Server tables. These are important database objects.

Chief Key Constraints

A 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 chosen the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique information, they are frequently defined on an identity column.

When you specify a principal fundamental constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This alphabetize also permits fast access to information when the main key is used in queries. If a primary primal constraint is divers on more than one column, values may be duplicated within one cavalcade, but each combination of values from all the columns in the primary cardinal constraint definition must be unique.

As shown in the post-obit analogy, the ProductID and VendorID columns in the Purchasing.ProductVendor tabular array grade a composite primary central 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.

Composite PRIMARY KEY constraint

  • A table can contain only one main key constraint.

  • A primary key cannot exceed 16 columns and a total fundamental length of 900 bytes.

  • The index generated by a chief cardinal constraint cannot crusade the number of indexes on the table to exceed 999 nonclustered indexes and 1 amassed index.

  • If amassed or nonclustered is not specified for a primary key constraint, clustered is used if there no amassed index on the table.

  • All columns defined inside a primary cardinal constraint must be divers equally not null. If nullability is non specified, all columns participating in a primary key constraint have their nullability ready to not null.

  • If a primary key is defined on a CLR user-defined type cavalcade, the implementation of the type must back up binary ordering.

Foreign Key Constraints

A strange key (FK) is a cavalcade or combination of columns that is used to establish and enforce a link betwixt the data in two tables to control the data that can be stored in the foreign fundamental tabular array. In a strange key reference, a link is created between two tables when the column or columns that agree the principal primal 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 tabular array has a foreign fundamental link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. The SalesPersonID column in the SalesOrderHeader tabular array matches the master cardinal column of the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign cardinal to the SalesPerson tabular array. By creating this foreign fundamental human relationship, a value for SalesPersonID cannot be inserted into the SalesOrderHeader table if it does non already exist in the SalesPerson table.

A tabular array can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2022 (xiii.ten) increases the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 to x,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:

  • Greater than 253 foreign key references are only supported for DELETE DML operations. UPDATE and MERGE operations are not supported.

  • A table with a foreign key reference to itself is yet express to 253 foreign key references.

  • Greater than 253 foreign key references are non currently available for columnstore indexes, retention-optimized tables, Stretch Database, or partitioned strange key tables.

Indexes on Foreign Cardinal Constraints

Unlike primary key constraints, creating a foreign key constraint does not automatically create a respective alphabetize. However, manually creating an index on a foreign cardinal is often useful for the following reasons:

  • Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the cavalcade or columns in the foreign key constraint of ane table with the primary or unique key column or columns in the other tabular array. An index enables the Database Engine to rapidly find related data in the foreign fundamental table. However, creating this index is not required. Data from two related tables can be combined even if no primary key or foreign key constraints are divers between the tables, only a foreign key relationship betwixt ii tables indicates that the 2 tables take been optimized to be combined in a query that uses the keys as its criteria.

  • Changes to master key constraints are checked with foreign key constraints in related tables.

Referential Integrity

Although the main purpose of a foreign key constraint is to control the data that can be stored in the strange primal 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 cleaved; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

A strange key constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the principal cardinal tabular array if those changes invalidate the link to data in the strange key table. If an attempt is made to delete the row in a primary fundamental tabular array or to change a main key value, the activity volition fail when the deleted or inverse main primal value corresponds to a value in the strange key constraint of another table. To successfully change or delete a row in a foreign key constraint, y'all must starting time either delete the foreign key information in the foreign key table or change the foreign fundamental data in the foreign key table, which links the foreign primal to unlike primary central information.

Cascading Referential Integrity

By using cascading referential integrity constraints, you can define the actions that the Database Engine takes when a user tries to delete or update a cardinal to which existing foreign keys point. The post-obit cascading actions tin be defined.

NO Action
The Database Engine raises an fault and the delete or update action on the row in the parent tabular array is rolled back.

Cascade
Respective rows are updated or deleted in the referencing tabular array when that row is updated or deleted in the parent table. Cascade cannot be specified if a timestamp column is part of either the strange key or the referenced central. ON DELETE CASCADE cannot exist specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULL
All the values that make up the foreign primal are set up to NULL when the corresponding row in the parent table is updated or deleted. For this constraint to execute, the foreign key columns must be nullable. Cannot exist specified for tables that have INSTEAD OF UPDATE triggers.

Prepare DEFAULT
All the values that brand up the foreign key are set up to their default values if the respective row in the parent tabular array is updated or deleted. For this constraint to execute, all foreign cardinal columns must have default definitions. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the cavalcade. Cannot exist specified for tables that have INSTEAD OF UPDATE triggers.

Pour, Set Null, Fix DEFAULT and NO Activeness can be combined on tables that have referential relationships with each other. If the Database Engine encounters NO Activeness, information technology stops and rolls back related CASCADE, SET Zero and SET DEFAULT actions. When a DELETE statement causes a combination of Cascade, Gear up 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 Actions

Cascading referential deportment fire the AFTER UPDATE or Afterward DELETE triggers in the following fashion:

  • All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.

  • If in that location are any Afterwards triggers defined on the afflicted tables, these triggers fire after all cascading deportment are performed. These triggers fire in contrary lodge of the cascading action. If in that location are multiple triggers on a unmarried table, they burn in random order, unless at that place is a dedicated starting time or concluding trigger for the tabular array. This order is every bit specified past using sp_settriggerorder.

  • If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the lodge in which these chains burn their respective triggers is unspecified. However, one concatenation e'er fires all its triggers before another chain starts firing.

  • An AFTER trigger on the table that is the directly target of an UPDATE or DELETE action fires regardless of whether any rows are affected. There are no other tables affected by cascading in this case.

  • If any one of the previous triggers performs UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. These secondary bondage are candy for each UPDATE or DELETE functioning at a time after all triggers on all principal chains burn. This process may be recursively repeated for subsequent UPDATE or DELETE operations.

  • Performing CREATE, Change, DELETE, or other data definition linguistic communication (DDL) operations inside the triggers may cause DDL triggers to burn. This may after perform DELETE or UPDATE operations that start boosted cascading chains and triggers.

  • If an error is generated inside any detail cascading referential action chain, an error is raised, no Later on triggers are fired in that chain, and the DELETE or UPDATE performance that created the concatenation is rolled back.

  • A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. However, an Afterwards trigger on a table targeted by a cascading activeness tin can execute an INSERT, UPDATE, or DELETE statement on some other table or view that fires an INSTEAD OF trigger defined on that object.

Next steps

The following tabular array lists the common tasks associated with primary fundamental and foreign central constraints.

Job Commodity
Describes how to create a principal key. Create Chief Keys
Describes how to delete a main key. Delete Master Keys
Describes how to modify a chief key. Modify Primary Keys
Describes how to create foreign key relationships Create Foreign Key Relationships
Describes how to modify foreign key relationships. Change Foreign Key Relationships
Describes how to delete strange key relationships. Delete Foreign Key Relationships
Describes how to view foreign key properties. View Foreign Key Properties
Describes how to disable foreign key constraints for replication. Disable Foreign Primal Constraints for Replication
Describes how to disable foreign fundamental constraints during an INSERT or UPDATE argument. Disable Foreign Key Constraints with INSERT and UPDATE Statements