Foreign Key Delete Actions

Multiple Child Rows Affected

Several child rows can be affected by one parent delete.

highlighted = computed this step

Multiple child rows affected

More than one child row can point at the same parent.

one parent, many children\text{one parent, many children}

Delete one parent

The cascade is deleted and affects 2 child rows for the same parent key.

affected=2\text{affected}=2

Foreign-key delete-action examples are tiny finite table checks; SET NULL, deferrable checks, triggers, transactions, locks, timing, indexing, performance, general referential-action completeness, and product behavior are out of scope.

Multiple child rows affected: parent rowscustomer_idname1Ada2Ben3Cy4Dee Multiple child rows affected: child rowsorder_idcustomer_idtotal101150102230103170104NULL5 Child-to-parent referenceschildRowchildValueparentRowstatus010matches_parent121matches_parent210matches_parent3NULLnoneNULL_no_reference Delete resultfieldvalueactioncascadeparentValue1parentRow0statusdeletedchildRowsAffected(0, 2) Parent after deletecustomer_idname2Ben3Cy4Dee Child after deleteorder_idcustomer_idtotal102230104NULL5 FK delete factsfactvalueparentRowCount4childRowCount4referenceCount4nullChildKeyCount1affectedChildCount2deleteStatusdeletedparentAfterCount3childAfterCount2supportedActionsrestrict,cascade

Read both child rows

Both affected child row numbers appear in childRowsAffected.

two rows removed\text{two rows removed}

Summary

Cascade follows all matching child rows in this finite table.

all matches\text{all matches}