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

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

主要内容：

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

一、初始工作

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

 

--Create Table
IFEXISTS (SELECT[name]FROM dbo.sysobjects WHERE[name]='Tree'AND type='u' )
    BEGIN 
        IFEXISTS(SELECT[name]FROM dbo.sysobjects WHERE[name]='Info'AND type='u')
            DROPTABLE Info
        DROPTABLE Tree
    END
ELSE
    BEGIN
        CREATETABLE Tree 
        (
            id BIGINTPRIMARYKEY,
            [name]NVARCHAR(50) NOTNULL,
            parentID BIGINTFOREIGNKEYREFERENCES Tree(id) ONDELETE NO ACTION NOTNULL
        )
    END
IFEXISTS(SELECT[name]FROM dbo.sysobjects WHERE[name]='Info'AND type='u')
    DROPTABLE Info
ELSE
    BEGIN
        CREATETABLE Info
        (
            id BIGINTPRIMARYKEYFOREIGNKEYREFERENCES Tree(id) ONDELETECASCADE,
            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 Data
DELETEFROM dbo.Tree
DELETEFROM dbo.Info
INSERTINTO dbo.Tree VALUES(1,'A',1)
INSERTINTO dbo.Tree VALUES(2,'B',1)
INSERTINTO dbo.Tree VALUES(3,'C',1)
INSERTINTO dbo.Tree VALUES(4,'D',2)
INSERTINTO dbo.Tree VALUES(5,'E',2)
INSERTINTO dbo.Tree VALUES(6,'F',3)
INSERTINTO dbo.Tree VALUES(7,'G',3)
INSERTINTO dbo.Tree VALUES(8,'H',4)
INSERTINTO dbo.Tree VALUES(9,'I',4)
INSERTINTO dbo.Tree VALUES(10,'J',4)
INSERTINTO info VALUES(1,'AA')
INSERTINTO info VALUES(2,'BB')
INSERTINTO info VALUES(3,'CC')
INSERTINTO info VALUES(4,'DD')
INSERTINTO info VALUES(5,'EE')
INSERTINTO info VALUES(6,'FF')
INSERTINTO info VALUES(7,'GG')
INSERTINTO info VALUES(8,'HH')
INSERTINTO info VALUES(9,'II')
INSERTINTO info VALUES(10,'JJ')

 

 

二、SQL实现

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

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        KenshinCui
-- Create date: 2010.11.22
-- Description:    无限级联删除
-- =============================================
CREATEPROCEDURE NodeDelete
    @idBIGINT
AS
BEGIN
    DECLARE@tbIdsTABLE(id BIGINT)
    DECLARE@tempTbsTABLE(id BIGINT)
    DECLARE@tbTABLE(id BIGINT,orderIndex BIGINTIDENTITY(1,1))
    INSERTINTO@tbIds(id) VALUES(@id)
    INSERTINTO@tempTbs(id) VALUES(@id)
    INSERTINTO@tb(id) VALUES(@id)
    WHILEEXISTS(SELECT id FROM@tbIds)
        BEGIN
            DELETEFROM@tbIds
            INSERTINTO@tbSELECT ID FROM dbo.Tree WHERE ParentID IN (SELECT ID FROM@tempTbs)
            INSERTINTO@tbIdsSELECT ID FROM dbo.Tree WHERE ParentID IN (SELECT ID FROM@tempTbs)

            DELETEFROM@tempTbs
            INSERTINTO@tempTbsSELECT id FROM@tbIds
        END

    DECLARE@tidINT 
    DECLARE myCursor CURSORFORSELECT id FROM@tbORDERBY orderIndex DESC 
    OPEN myCursor 
    FETCHNEXTFROM myCursor INTO@tid 
    WHILE@@fetch_status=0 
    BEGIN
        DELETEFROM dbo.Tree WHERE ID =@tid

        FETCHNEXTFROM myCursor INTO@tid  
    END
    CLOSE myCursor   
    DEALLOCATE myCursor
END
GO

 

 

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

三、总结

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