I a mysql table named members that basically has two columns: parentID
and childID
. That way I can create a hierarchical tree based on these two columns, so for example:
parentID, ChildID
1,2
2,3
3,4
Will generate a tree in my application with parentID = 1
as the root and 2 as the first node, 3 as the second node, 4 as the third node and so forth.
If I want to delete all nodes from a given parentID
in this case, how can I accomplish this?
You just need to ensure that you have set up a foreign key from the child row to its parent, with the
ON DELETE CASCASDE
option set on the foreign key. This works equally well a self referencing table as it does to references in separate tables. In order to delete the tree, simply delete the parent node. All child rows will be summarily deleted.e.g. Given:
We can remove the whole of the first tree by simply deleting the root node:
SqlFiddle of same
Note however that from the Docs that there is a limit to the depth of
CASCADE
deletes: