sql server, cascade delete and parent/child table

Question

i have one simple table with following columns: id, name and parentID

i created relationship diagram between id and parentID (on a same table), like simple tree, on the same table, but when i tried to user cascade delete it was disabled for me

i know that it will be recursive delete if i will delete parent it will delete his children

have i any options for anable cascade delete without triggers?

Answer 1

No, SQL Server does not allow recursive and/or multiple cascade paths.

You can use a stored procedure to delete bit by bit, or use a trigger. Sorry.

Answer 2

best to use an after trigger for multiple actions.

Answer 3

One option that may work for you:

• Place ParentID and ChildID in a separate table, remove ParentID from the base table
• Cascade delete based solely on ID-->ParentID

The problem is that when you delete a child, you won't delete the link from its parent, and when you delete a parent, you won't delete any links from its children to any grandchildren.

However, you can get around this in your app by always using an INNER JOIN against both the ParentID and ChildID, so any leftover fluff in the Parent/Child table will be ignored.

You can then run a stored procedure on whatever timed basis you want to clean out any Parent/Child relationships where either the parent or child do not exist. Rinse and repeat each time you run it, since a simple DELETE FROM parentchild WHERE NE(parent) OR NE(child) won't be recursive.