树节点级联删除java_SQL之树形结构无限级联删除

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

主要内容：

初始工作

SQL实现

总结

一、初始工作

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

代码

--Create TableIFEXISTS(SELECT[name]FROMdbo.sysobjectsWHERE[name]='Tree'ANDtype='u')BEGINIFEXISTS(SELECT[name]FROMdbo.sysobjectsWHERE[name]='Info'ANDtype='u')DROPTABLEInfoDROPTABLETreeENDELSEBEGINCREATETABLETree

(

idBIGINTPRIMARYKEY,[name]NVARCHAR(50)NOTNULL,

parentIDBIGINTFOREIGNKEYREFERENCESTree(id)ONDELETENO ACTIONNOTNULL)ENDIFEXISTS(SELECT[name]FROMdbo.sysobjectsWHERE[name]='Info'ANDtype='u')DROPTABLEInfoELSEBEGINCREATETABLEInfo

(

idBIGINTPRIMARYKEYFOREIGNKEYREFERENCESTree(id)ONDELETECASCADE,

infoNVARCHAR(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 DataDELETEFROMdbo.TreeDELETEFROMdbo.InfoINSERTINTOdbo.TreeVALUES(1,'A',1)INSERTINTOdbo.TreeVALUES(2,'B',1)INSERTINTOdbo.TreeVALUES(3,'C',1)INSERTINTOdbo.TreeVALUES(4,'D',2)INSERTINTOdbo.TreeVALUES(5,'E',2)INSERTINTOdbo.TreeVALUES(6,'F',3)INSERTINTOdbo.TreeVALUES(7,'G',3)INSERTINTOdbo.TreeVALUES(8,'H',4)INSERTINTOdbo.TreeVALUES(9,'I',4)INSERTINTOdbo.TreeVALUES(10,'J',4)INSERTINTOinfoVALUES(1,'AA')INSERTINTOinfoVALUES(2,'BB')INSERTINTOinfoVALUES(3,'CC')INSERTINTOinfoVALUES(4,'DD')INSERTINTOinfoVALUES(5,'EE')INSERTINTOinfoVALUES(6,'FF')INSERTINTOinfoVALUES(7,'GG')INSERTINTOinfoVALUES(8,'HH')INSERTINTOinfoVALUES(9,'II')INSERTINTOinfoVALUES(10,'JJ')

二、SQL实现

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

代码

SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO--=============================================--Author: KenshinCui--Create date: 2010.11.22--Description: 无限级联删除--=============================================CREATEPROCEDURENodeDelete@idBIGINTASBEGINDECLARE@tbIdsTABLE(idBIGINT)DECLARE@tempTbsTABLE(idBIGINT)DECLARE@tbTABLE(idBIGINT,orderIndexBIGINTIDENTITY(1,1))INSERTINTO@tbIds(id)VALUES(@id)INSERTINTO@tempTbs(id)VALUES(@id)INSERTINTO@tb(id)VALUES(@id)WHILEEXISTS(SELECTidFROM@tbIds)BEGINDELETEFROM@tbIdsINSERTINTO@tbSELECTIDFROMdbo.TreeWHEREParentIDIN(SELECTIDFROM@tempTbs)INSERTINTO@tbIdsSELECTIDFROMdbo.TreeWHEREParentIDIN(SELECTIDFROM@tempTbs)DELETEFROM@tempTbsINSERTINTO@tempTbsSELECTidFROM@tbIdsENDDECLARE@tidINTDECLAREmyCursorCURSORFORSELECTidFROM@tbORDERBYorderIndexDESCOPENmyCursorFETCHNEXTFROMmyCursorINTO@tidWHILE@@fetch_status=0BEGINDELETEFROMdbo.TreeWHEREID=@tidFETCHNEXTFROMmyCursorINTO@tidENDCLOSEmyCursorDEALLOCATEmyCursorENDGO

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

三、总结

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