摘要:树形结构的删除存在其自身特点,特别对于无限级联的树形结构更是如此,今天我们一块看一下如何处理无限级联树的删除问题。
主要内容:
初始工作
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)"的方式删除,因为子句中排序是有约束的(不是任何时候都可以的),这里我们是通过游标来解决的(当然还有别的方式)。