SQL之树形结构无限级联删除

摘要：树形结构的删除存在其自身特点，特别对于无限级联的树形结构更是如此，今天我们一块看一下如何处理无限级联树的删除问题。

主要内容：

1. 初始工作
2. SQL实现
3. 总结

一、初始工作

为了更好的说明问题，我首先建立两张表：

--Create TableIF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name]='Tree' AND type='u' )BEGIN IF EXISTS(SELECT [name] FROM dbo.sysobjects WHERE [name]='Info' AND type='u')DROP TABLE InfoDROP TABLE TreeENDELSEBEGINCREATE TABLE Tree (id BIGINT PRIMARY KEY,[name] NVARCHAR(50) NOT NULL,parentID BIGINT FOREIGN KEY REFERENCES Tree(id) ON DELETE NO ACTION NOT NULL)ENDIF EXISTS(SELECT [name] FROM dbo.sysobjects WHERE [name]='Info' AND type='u')DROP TABLE InfoELSEBEGINCREATE TABLE Info(id BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Tree(id) ON DELETE CASCADE,info NVARCHAR(500))END

这里我们建立了两张表："Tree"和"Info"。"Tree"作为我们的树形结构信息存放表，里面包含节点编号、节点名称和父类编号；"Info"表中存放每个节点的各种信息（当然可以有多张"Info"表,这里简单起见只有一个信息表）。

到了这里可能会有朋友说：在创建表的时候直接在"parentID"后面加上"DELETE CASCADE"问题不就解决了吗？由于"Tree"表是自身关联的，这样一来删除其父类的话就会将子类删除？何必弄的那么麻烦呢？如果真的这样的话我想再好不过了，今天的话题也就简单了。事实上那样是不可行的，SQL Server会抛出如下错误告诉你那样做是不可以的（其实这也是自身关联的特点：不能设定"DELETE CASCADE"，当然对于Info表式没有问题的）：

消息1785，级别16，状态0，第1 行

将FOREIGN KEY 约束'FK__Tree__parentID__07F6335A' 引入表'Tree' 可能会导致循环或多重级联路径。请指定ON DELETE NO ACTION 或ON UPDATE NO ACTION，或修改其他FOREIGN KEY 约束。

消息1750，级别16，状态0，第1 行

无法创建约束。请参阅前面的错误消息。

接着我们插入一些测试数据(姑且认为顶级节点父类为其自身)：

-- Insert DataDELETE FROM dbo.TreeDELETE FROM dbo.InfoINSERT INTO dbo.Tree VALUES(1,'A',1)INSERT INTO dbo.Tree VALUES(2,'B',1)INSERT INTO dbo.Tree VALUES(3,'C',1)INSERT INTO dbo.Tree VALUES(4,'D',2)INSERT INTO dbo.Tree VALUES(5,'E',2)INSERT INTO dbo.Tree VALUES(6,'F',3)INSERT INTO dbo.Tree VALUES(7,'G',3)INSERT INTO dbo.Tree VALUES(8,'H',4)INSERT INTO dbo.Tree VALUES(9,'I',4)INSERT INTO dbo.Tree VALUES(10,'J',4)INSERT INTO info VALUES(1,'AA')INSERT INTO info VALUES(2,'BB')INSERT INTO info VALUES(3,'CC')INSERT INTO info VALUES(4,'DD')INSERT INTO info VALUES(5,'EE')INSERT INTO info VALUES(6,'FF')INSERT INTO info VALUES(7,'GG')INSERT INTO info VALUES(8,'HH')INSERT INTO info VALUES(9,'II')INSERT INTO info VALUES(10,'JJ')

二、SQL实现

有了表和数据我们就开始思考如何解决级联删除的问题吧。既然是无限级联，也就是说根本不知道深度，当然最简单的方法就是使用递归或者通过循环来实现。姑且不论这种方法如何实现，关键是这种方法删除的时候只会从上往下删除（也就是从父节点到子节点的顺序），而由于外键约束的关系我们这样删除是不可行的。因此，我们必须找到一种能够从最底端的子节点依次往上删除的方法。下面我们直接看一下SQL：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:KenshinCui-- Create date: 2010.11.22-- Description:无限级联删除-- =============================================CREATE PROCEDURE NodeDelete@id BIGINTASBEGINDECLARE @tbIds TABLE(id BIGINT)DECLARE @tempTbs TABLE(id BIGINT)DECLARE @tb TABLE(id BIGINT,orderIndex BIGINT IDENTITY(1,1))INSERT INTO @tbIds(id) VALUES(@id)INSERT INTO @tempTbs(id) VALUES(@id)INSERT INTO @tb(id) VALUES(@id)WHILE EXISTS(SELECT id FROM @tbIds)BEGINDELETE FROM @tbIdsINSERT INTO @tb SELECT ID FROM dbo.Tree WHERE ParentID IN (SELECT ID FROM @tempTbs)INSERT INTO @tbIds SELECT ID FROM dbo.Tree WHERE ParentID IN (SELECT ID FROM @tempTbs)DELETE FROM @tempTbsINSERT INTO @tempTbs SELECT id FROM @tbIdsENDDECLARE @tid INT DECLARE myCursor CURSOR FOR SELECT id FROM @tb ORDER BY orderIndex DESC OPEN myCursor FETCH NEXT FROM myCursor INTO @tid WHILE @@fetch_status=0 BEGINDELETE FROM dbo.Tree WHERE ID =@tidFETCH NEXT FROM myCursor INTO @tid ENDCLOSE myCursor DEALLOCATE myCursorENDGO

这种方法的思路就是通过从上到下的顺序依次查找，首先将查找的内容放到一个Table类型的变量中，而这个变量本身就有一个排序字段可以排序。这样一来我们通过第一次遍历就可以将所要删除的节点id有序的存储到变量中，接着我们再通过倒序遍历的方式遍历这个变量执行删除。

三、总结

这问题关键注意以下几点：第一通过父类节点找子类节点时不一定只有一个子节点，找到的是一个集合，我们要通过一种类型来存放集合变量（也就是上面的table类型）；第二就是如何将table变量作为堆栈使用（也就是顺序要可控的）；第三是在第一循环之后得到的table型变量无法直接通过"delete from Tree where id in（select id from @tb order by desc）"的方式删除，因为子句中排序是有约束的（不是任何时候都可以的），这里我们是通过游标来解决的（当然还有别的方式）。