sql cascading deletes

Question

I have a question about cascading deletes and foreign key references: Let's assume the following tables:

ITEMX                 | ITEMY
------------------    | ------------
ID    FKID_ITEMY      | ID
1     1               | 1
2     1               |

There is a cascade delete on FKID_ITEMY such that if I delete a row in ITEMX, the corresponding item in ITEMY will be deleted. My question is:

Will the delete of row ID 1 in ITEMX delete row ID 1 in ITEMY?

Or will just delete row ID 1 in ITEMX because row ID 2 is still referencing row ID 1 in ITEMY?

In other words, I would like to see row ID 1 of ITEMY deleted when there are no more references to it. Will a cascade delete achieve this?

Answer 1

You've got the concept of cascading deletes backwards.

Even though you declare FKID_ITEMY with a cascading delete option, nothing happens if you delete ITEMX where ID=1.

The cascading behavior affects what happens if the referenced row in ITEMY gets deleted. If you delete ITEMY where ID=1, it will delete both rows in ITEMX that reference that row.

You declare the cascading behavior on the foreign key in a dependent table, because you could have another dependent table ITEMZ, and you want it to behave differently. E.g. if you try to delete a row in ITEMY then you want that to be cancelled if there exist any referencing rows in ITEMZ.