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

Answer 2

Have you tried with mysql JOIN example to fullfill yor action.

Please check at sql fiddle.

I have set this example by query using inner join. If you pass WHERE mem1.parentID = '2' then it will remove all records having 2 into ChildID OR into parentID field.

DELETE mem2.* FROM
  member mem1
  INNER JOIN member mem2 ON mem2.ChildID = mem1.parentID OR mem1.parentID = mem2.parentID
  WHERE mem1.parentID = '2';

Above DELETE query would delete column which have parentID = '2' and same value in childID. So output is, two columns removed from member table.

Please check and let me know if you face any further query/concern.

Answer 3

Basically, there are two ways:

1. Recursively loop and find all the record need to be deleted

   SELECT ChildID FROM MyTable WHERE parentID = ?
   

   You get the list of ChildID by this SQL, and run this SQL again with list of ChildID as 'parentID' until you get a empty list. After that, you delete it all

   DELETE FROM MyTable WHERE ChildID = ?
   

2. Use nested set model. You can find many tutorial on internet, example: managing hierachical data in my sql. Then you can delete what you want:

   DELETE w FROM MyTable w 
   INNER JOIN MyTable parent
   ON w.parentID = parent.ChildID
   WHERE w.lft BETWEEN parent.lft AND parent.rgt
       AND parent.parentID = ?