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.

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

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