Cascading Referential Integrity Constraints
By using cascading referential integrity constraints, you can define the actions that the SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.
The REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements support the ON DELETE and ON UPDATE clauses. Cascading actions can also be defined by using the Foreign Key Relationships dialog box:
- [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
- [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
NO ACTION is the default if ON DELETE or ON UPDATE is not specified.
- ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE statement is rolled back.
- ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE statement is rolled back.
CASCADE, SET NULL and SET DEFAULT allow for deletions or updates of key values to affect the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. If cascading referential actions have also been defined on the target tables, the specified cascading actions also apply for those rows deleted or updated. CASCADE cannot be specified for any foreign keys or primary keys that have a timestamp column.
- ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.
ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key.Note
CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key.
- ON DELETE SET NULL
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.
- ON UPDATE SET NULL
Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.
- ON DELETE SET DEFAULT
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any nonnull values that are set because of ON DELETE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.
- ON UPDATE SET DEFAULT
Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any non-null values that are set because of ON UPDATE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.
Consider the FK_ProductVendor_Vendor_VendorID constraint on the Purchasing.ProductVendor table in AdventureWorks. This constraint establishes a foreign key relationship from the VendorID column in the ProductVendor table to the VendorID primary key column in the Purchasing.Vendor table. If ON DELETE CASCADE is specified for the constraint, deleting the row in Vendor where VendorID equals 100 also deletes the three rows in ProductVendor where VendorID equals 100. If ON UPDATE CASCADE is specified for the constraint, updating the VendorID value in the Vendor table from 100 to 155 also updates the VendorID values in the three rows in ProductVendor whose VendorID values currently equal 100.
ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. For tables that have INSTEAD OF UPDATE triggers, the following cannot be specified: ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UDATE SET DEFAULT.
Multiple Cascading Actions
Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.
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 or UPDATE 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 is 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 or DELETE 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 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 perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. This process may be recursively repeated for subsequent UPDATE or DELETE operations.
Performing CREATE, ALTER, DELETE, or other data definition language (DDL) operations inside the triggers may cause DDL triggers to fire. This may 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 cannot also have a REFERENCES clause that specifies a cascading action. However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.
Cascading Referential Constraints Catalog Information
Querying the sys.foreign_keys catalog view returns the following values that indicate the cascading referential constraint specified for a foreign key.
| Value | Description |
|---|---|
0 |
NO ACTION |
1 |
CASCADE |
2 |
SET NULL |
3 |
SET DEFAULT |
The UPDATE_RULE and DELETE_RULE columns returned by sp_fkeys and sp_foreignkeys return 0 when CASCADE, SET NULL, or SET DEFAULT is specified; and return 1 when NO ACTION is specified or is the default.
When a foreign key is specified as the object of sp_help, the output result set contains the following columns.
| Column name | Data type | Description |
|---|---|---|
delete_action |
nvarchar(9) |
Indicates whether the delete action is CASCADE, SET NULL, SET DEFAULT, NO ACTION, or N/A (not applicable). |
update_action |
nvarchar(9) |
Indicates whether the update action is CASCADE, SET NULL, SET DEFAULT, NO ACTION, or N/A (not applicable). |
See Also
Other Resources
CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
DROP TABLE (Transact-SQL)
sp_settriggerorder (Transact-SQL)
sys.foreign_keys (Transact-SQL)
sp_fkeys (Transact-SQL)
sp_foreignkeys (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|