Cascading Deletes and Updates of Primary Keys

SQL Server restricts the data in related tables to values that preserve
data integrity.

Microsoft SQL Server provides declarative referential integrity (DRI), which allows you to define data integrity restrictions for a table and relationships between tables, both of which are automatically enforced by SQL Server at the system level.

SQL Server conforms to ANSI Entry SQL with regard to referential integrity between primary key and foreign key columns. This requires the inserting, updating, and deleting of data in related tables to be restricted to values that preserve referential integrity.

ANSI Intermediate SQL adds "referential actions," which describe what to do with dependent foreign key values when their corresponding primary key values are updated or deleted.

This section describes how these cascading deletes and updates can be implemented with SQL Server.

DRI preserves the defined relationships between tables when records are inserted, updated, or deleted. DRI is based on FOREIGN KEY, PRIMARY KEY, and UNIQUE constraint relationships, and it ensures that related key values are consistent. SQL Server uses PRIMARY KEY, FOREIGN KEY or REFERENCES, and UNIQUE constraints to enforce DRI; a foreign key can refer to a column that is declared as either a primary key or declared with a UNIQUE constraint.

The Restrict-Only level of referential integrity requires that the following four actions be detected and prevented from completing successfully:

Inserting a row in a referencing table where the value of a foreign key does not match a primary key value in the referenced table.
Updating a foreign key value in a row in a referencing table so there is no matching primary key value in the referenced table.
Updating a primary key value in a row in a referenced table so a foreign key in a row in the referencing table no longer has a matching primary key value.
Deleting a row in a referenced table so a foreign key in a row in the referencing table no longer has a matching primary key.

Cascading updates and deletes provide an alternative to merely restricting the occurrence of the two preceding actions. When a primary key is updated, as in the third case above, a cascading update causes all referencing foreign keys to be updated to the new primary key value. When a primary key is deleted, as in the last case, a cascading delete performs one of three actions:

Deletes the rows that referenced the deleted primary key (CASCADE).
Sets the referencing foreign key values to NULL (SET NULL).
Sets the referencing foreign key values to the column's default value (SET DEFAULT).

Cascading updates and deletes (of primary keys) can be implemented by using either triggers or stored procedures. Descriptions of each are given below. The use of stored procedures is preferred because the cascading functionality can coexist with declared foreign keys. To use triggers to supply the same functionality, foreign keys cannot be declared.

Given the definition of a primary key, changing a primary key value should be a relatively rare occurrence; deleting a primary key should be a less rare operation.