Using the ON DELETE CASCADE Option

Use the ON DELETE CASCADE option if you want rows deleted from the child table when the DELETE or MERGE statement removes corresponding rows from the parent table.

Here the parent table is the table specified in the REFERENCING clause of the definition of an enabled foreign key constraint, and the child table is the table on which the enabled foreign key constraint is defined. If you do not specify cascading deletes, the default behavior of the database server prevents DELETE and MERGE statements from deleting data in a table that another tables references within a primary-key foreign-key relationship.

If you specify this option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The advantage of the ON DELETE CASCADE option is that it allows you to reduce the quantity of SQL statements needed to perform delete actions.

For example, in the stores_demo database, the stock table contains the stock_num column as a primary key. The catalog table refers to the stock_num column as a foreign key. The following ALTER TABLE statements drop an existing foreign-key constraint (without cascading delete) and add a new constraint that specifies cascading deletes:

ALTER TABLE catalog DROP CONSTRAINT aa;

ALTER TABLE catalog ADD CONSTRAINT
   (FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   ON DELETE CASCADE CONSTRAINT ab);

With cascading deletes specified on the child table, in addition to deleting a stock item from the stock table, the delete cascades to the catalog table that is associated with the stock_num foreign key. This cascading delete works only if the stock_num that you are deleting was not ordered; otherwise, the constraint from the items table would disallow the cascading delete. For more information, see Restrictions on DELETE When Tables Have Cascading Deletes.

If a table has a trigger with a DELETE trigger event, you cannot define a cascading-delete referential constraint on that table. You receive an error when you attempt to add a referential constraint that specifies ON DELETE CASCADE to a table that has a delete trigger.

The TRUNCATE statement cannot result in cascading deletes from a child table. The target table of the TRUNCATE statement cannot be referenced in the definition of an enabled foreign-key constraint on another table (unless that child table has no rows).

For information about syntax restrictions and locking implications when you delete rows from tables that have cascading deletes, see Considerations When Tables Have Cascading Deletes.