当parentID和childID在同一张表上时，如何删除级联? [英] How to delete cascade when parentID and childID are on the same table?

问题描述

我是一个名为成员的mysql表，该表基本上具有两列:parentID和childID.这样，我可以基于这两列创建一个层次树，例如:

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

将在我的应用程序中生成一个树，以parentID = 1作为根，2作为第一个节点，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.

在这种情况下，如果我想从给定的parentID中删除所有节点，该怎么做?

If I want to delete all nodes from a given parentID in this case, how can I accomplish this?

推荐答案

您只需要确保已在子行到其父行之间设置了外键，并在外键上设置了ON DELETE CASCASDE选项.自引用表和单独表中的引用一样，效果很好.为了删除树，只需删除父节点.所有子行将被立即删除.

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.

例如鉴于:

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

但是请注意，从文档 CASCADE删除内容的深度受到限制:

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

级联操作的嵌套深度不得超过15个级别

Cascading operations may not be nested more than 15 levels deep

这篇关于当parentID和childID在同一张表上时，如何删除级联?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！