Foreign Key Delete Actions

Delete Parent with No Children

A parent with no referencing children can be deleted.

highlighted = computed this step

Delete parent with no children

If no child row points at the parent, RESTRICT has nothing to block.

no child rows affected\text{no child rows affected}

Delete Dee

The delete is deleted, affects 0 child rows, and leaves 3 parent rows.

affected=0\text{affected}=0

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.

Delete parent with no children: parent rowscustomer_idname1Ada2Ben3Cy4Dee Delete parent with no children: child rowsorder_idcustomer_idtotal101150102230103170104NULL5 Child-to-parent referenceschildRowchildValueparentRowstatus010matches_parent121matches_parent210matches_parent3NULLnoneNULL_no_reference Delete resultfieldvalueactionrestrictparentValue4parentRow3statusdeletedchildRowsAffected() Parent after deletecustomer_idname1Ada2Ben3Cy Child after deleteorder_idcustomer_idtotal101150102230103170104NULL5 FK delete factsfactvalueparentRowCount4childRowCount4referenceCount4nullChildKeyCount1affectedChildCount0deleteStatusdeletedparentAfterCount3childAfterCount4supportedActionsrestrict,cascade

Read after tables

The parent row is gone, and the child table is unchanged.

child unchanged\text{child unchanged}

Summary

A successful parent delete is visible in the after table.

parent after delete\text{parent after delete}