How to delete cascade when parentID and childID are on the same table?

Question

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?

Answer 1

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:

CREATE TABLE MyTable
(
  ID INT NOT NULL PRIMARY KEY,
  ParentID INT  NULL,
  CONSTRAINT FK_MT_Parent FOREIGN KEY (ParentID) REFERENCES MyTable(ID) ON DELETE CASCADE
);

-- And inserting two trees of data:
-- 1-2-3
--   └-4
-- 10 - 11
INSERT INTO MyTable(ID,ParentID) VALUES
    (1,null), (2,1), (3,2), (4,2),
    (10,null), (11,10);

We can remove the whole of the first tree by simply deleting the root node:

DELETE FROM MYTable WHERE ID = 1;

SqlFiddle of same

Note however that from the Docs that there is a limit to the depth of CASCADE deletes:

Cascading operations may not be nested more than 15 levels deep