Primary and foreign key constraints
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Primary 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 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 called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they're 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 can 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.
- A table can contain only one primary key constraint.
- A primary key can't exceed 16 columns and a total key length of 900 bytes.
- The index generated by a primary key constraint can't cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.
- If clustered or nonclustered isn't specified for a primary key constraint, clustered is used if there's no clustered index on the table.
- All columns defined within a primary key constraint must be defined as not null. If nullability isn't specified, all columns participating in a primary key constraint have their nullability set to not null.
- If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering.
Foreign key constraints
A 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's 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
can't be inserted into the SalesOrderHeader
table if it doesn't 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:
Greater than 253 foreign key references are only supported for
DELETE
DML operations.UPDATE
andMERGE
operations aren't supported.A table with a foreign key reference to itself is still limited to 253 foreign key references.
Greater than 253 foreign key references aren't currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.
Important
Stretch Database is deprecated in SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Indexes on foreign key constraints
Unlike primary key constraints, creating a foreign key constraint doesn't automatically create a corresponding index. However, manually creating an index on a foreign key is often useful for the following reasons:
Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table. However, creating this index isn't required. Data from two related tables can be combined even if no primary key or foreign key constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.
Changes to primary 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 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 can't 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 fails 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 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 key to which existing foreign keys point. The following cascading actions can be defined.
NO ACTION
The Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.
CASCADE
Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table.
CASCADE
can't be specified if a timestamp column is part of either the foreign key or the referenced key.ON DELETE CASCADE
can't be specified for a table that has anINSTEAD OF DELETE
trigger.ON UPDATE CASCADE
can't be specified for tables that haveINSTEAD OF UPDATE
triggers.SET NULL
All the values that make up the foreign key are set 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. Can't be specified for tables that haveINSTEAD OF UPDATE
triggers.SET DEFAULT
All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable, and there's no explicit default value set,
NULL
becomes the implicit default value of the column. Can't be specified for tables that haveINSTEAD OF UPDATE
triggers.
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
, or 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 actions fire the AFTER UPDATE
or AFTER DELETE
triggers in the following manner:
All the cascading referential actions directly caused by the original
DELETE
orUPDATE
are performed first.If there are any
AFTER
triggers defined on the affected tables, these triggers fire after all cascading actions are performed. These triggers fire in opposite order of the cascading action. If there are multiple triggers on a single table, they fire in random order, unless there's a dedicated first or last trigger for the table. This order is as specified by using sp_settriggerorder.If multiple cascading chains originate from the table that was the direct target of an
UPDATE
orDELETE
action, the order in which these chains fire their respective triggers is unspecified. However, one chain always fires all its triggers before another chain starts firing.An
AFTER
trigger on the table that is the direct target of anUPDATE
orDELETE
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
orDELETE
operations on other tables, these actions can start secondary cascading chains. These secondary chains are processed for eachUPDATE
orDELETE
operation at a time after all triggers on all primary chains fire. This process can be recursively repeated for subsequentUPDATE
orDELETE
operations.Performing
CREATE
,ALTER
,DELETE
, or other data definition language (DDL) operations inside the triggers can cause DDL triggers to fire. This might subsequently perform DELETE or UPDATE operations that start additional cascading chains and triggers.If an error is generated inside any particular cascading referential action chain, an error is raised, no
AFTER
triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.A table that has an
INSTEAD OF
trigger can't also have aREFERENCES
clause that specifies a cascading action. However, anAFTER
trigger on a table targeted by a cascading action can execute anINSERT
,UPDATE
, orDELETE
statement on another table or view that fires anINSTEAD OF
trigger defined on that object.
Related content
- Create Primary Keys
- Delete Primary Keys
- Modify Primary Keys
- Create Foreign Key Relationships
- Modify Foreign Key Relationships
- Delete Foreign Key Relationships
- View Foreign Key Properties
- Disable Foreign Key Constraints for Replication
- Disable Foreign Key Constraints with INSERT and UPDATE Statements