Referential Constraints

Cascading referential integrity constraints allow you to define the actions the database server takes when a user attempts to delete or update a key to which existing foreign keys point.

If the REFERENCES clauses of the CREATE TABLE statements support ON DELETE and ON UPDATE clauses, the following options are available from within Database Design Studio:

ON DELETE { CASCADE | SET NULL | RESTRICT }

ON UPDATE { CASCADE | SET NULL | RESTRICT }

CASCADE

Allows deletions or updates of key values to cascade through 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 are also taken for the key values updated in those tables.

SET NULL

Specifies that if an attempt is made to update or delete a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are set to null for the key.

RESTRICT

Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the operation is rolled back.

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.

Logically a series of cascading referential actions triggered by a single DELETE or UPDATE should form a tree containing no circular references to the same rows. Some database servers, such as Microsoft SQL Server, stipulate that "no table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default." This can be limiting in a database design where other constraints are used to eliminate circular references.

The database designer should also be aware that some database servers do not allow cascading actions on certain data types and in conjunction with Triggers. You should refer to the documentation supplied by your vendor for DBMS specific restrictions.

CASCADE	Allows deletions or updates of key values to cascade through 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 are also taken for the key values updated in those tables.
SET NULL	Specifies that if an attempt is made to update or delete a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are set to null for the key.
RESTRICT	Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the operation is rolled back.