Foreign Key Delete Actions

Child Rows Point to a Parent

Non-NULL child keys are checked against parent keys.

highlighted = computed this step

Child rows point to a parent

Each non-NULL child key value is checked against the parent key values.

reference table\text{reference table}

Read the references

The compiler checks 4 child rows and finds 3 parent matches.

matches=3\text{matches}=3

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.

Child rows point to a parent: parent rowscustomer_idname1Ada2Ben3Cy4Dee Child rows point to a parent: 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 parentRow

The reference table shows the parent row number for each matching child.

parent row visible\text{parent row visible}

Summary

The delete rule later uses the same reference rows.

same links\text{same links}