Considerations When Tables Have Cascading Deletes

When you use the ON DELETE CASCADE option of the REFERENCES clause of either the CREATE TABLE or ALTER TABLE statement, you specify that you want deletes to cascade from one table to another. For example, in the stores_demo database, the stock table contains the column stock_num as a primary key. The catalog and items tables each contain the column stock_num as foreign keys with the ON DELETE CASCADE option specified. When a delete is performed from the stock table, rows are also deleted in the catalog and items tables, which are referenced through the foreign keys.

To have DELETE actions cascade to a table that has a referential constraint on a parent table, you need the Delete privilege only on the parent table that you reference in the DELETE statement.

4If a DELETE operation with no WHERE clause is performed 4on a table that one or more child tables reference with cascading deletes, 4Dynamic Server deletes all rows from that table and from any affected child 4tables. (This resembles the effect of the TRUNCATE statement, but Dynamic 4Server does not support TRUNCATE operations on any table that has a child 4table referencing it.)

For an example of how to create a referential constraint that uses cascading deletes, see Using the ON DELETE CASCADE Option.

Restrictions on DELETE When Tables Have Cascading Deletes

You cannot use a child table in a correlated subquery to delete a row from a parent table. If two child tables reference the same parent table, and one child specifies cascading deletes but the other child does not, then if you attempt to delete a row that applies to both child tables from the parent table, the delete fails, and no rows are deleted from the parent or child tables.

Locking and Logging Implications of Cascading Deletes

During deletes, the database server places locks on all qualifying rows of the referenced and referencing tables.

Dynamic Server requires transaction logging for cascading deletes. If logging is turned off in a database that is not ANSI-compliant, even temporarily, deletes do not cascade, because you cannot roll back any actions. For example, if a parent row is deleted, but the system fails before the child rows are deleted, the database will have dangling child records, in violation of referential integrity. After logging is turned back on, however, subsequent deletes cascade.