从SQL数据库中删除未连接的节点

Question

我在研究一种公司层级的树结构。所有数据都存储在Server数据库中。

数据库有两个具有ID和父节点ID的列(都是varchar(5))。

现在，当我移除一个节点时，该节点的所有子节点(及其子节点)都将留在数据库中--没有连接。我该怎么移除这些？

编辑:我正在使用https://www.codeproject.com/Articles/18378/Organization-Chart-Generator生成图表。

Answer 1

如果没有外键，这就行了。我正在创建一个名为节点的示例表，其中包含两个列，ID和父列。在我的版本中，它们是整数，但这并不重要。

create table nodes(id int, parent int)
insert nodes values (1,null),(2,1),(3,1),(4,2),(5,null)

select * from nodes

结果是：

id  parent
1   NULL
2   1
3   1
4   2
5   NULL

创建一个遍历关系的CTE

declare @target int; set @target=2
;with cte as 
(
select *, 1 as depth from nodes where id=@target
union all
select nodes.*, depth+1 from nodes
join cte on cte.id=nodes.parent
)
delete nodes where id in (select id from cte)

这就是结果

select * from nodes

1   NULL
3   1
5   NULL

如果您有外键，那么您将需要从最高的深度到最低的深度来查看，以避免错误。这将完成它(没有游标)

declare @temp table(id int, depth int)

declare @target int; set @target=2
;with cte as 
(
select *, 1 as depth from nodes where id=@target
union all
select nodes.*, depth+1 from nodes
join cte on cte.id=nodes.parent
)
insert @temp 
select id,depth from cte

while exists(select * from @temp)
begin
    delete nodes from nodes
    join @temp t on t.id=nodes.id
    where depth=(select max(depth) from @temp)

    delete @temp where depth=(select max(depth) from @temp)
end

结果是一样的。

Answer 2

你这里需要两样东西，

• 首先，您需要一个系统，以防止移除带有子程序的主程序。
• 第二，您需要一个程序来移除一个主程序及其所有的子类。

第一种方法是必要的，因此没有人可以通过使用任何其他方法来移除主人，然后才是正确的程序。

下面是如何设置这样一个系统的示例

create table test (
  id int not null identity,
  name varchar(10),
  parentid int  null,

  constraint pk_id primary key (id),  
  constraint fk_pid foreign key (parentid) references test (id) 
)

insert into test (name, parentid) 
values ('master', null), ('child1', 1), ('child2', 1), ('child3', 3)

现在，当您删除第一行(name=master)时，server将停止您，并返回一个错误，说明外键fk_pid被违反。

换句话说，只要主人还有孩子，就没有人能移除主人了。

现在，对于一个程序，可以删除一个主人和他所有的孩子，你可以看看其他的答案，只需选择一个你最喜欢的。

Answer 3

您可以使用递归过程来完成此操作:首先递归地删除节点的所有子程序，然后删除该节点。

CREATE PROCEDURE DELETE_NODE
  @NODE_ID int
AS
BEGIN
  declare @CHILD_NODE_ID int;    
  declare CHILDS cursor for select NODE_ID from MY_TABLE where PARENT_NODE_ID = @NODE_ID;

  open CHILDS;
  fetch next from CHILDS into @CHILD_NODE_ID;
  while @@fetch_status = 0 
  begin  
    exec DELETE_NODE @NODE_ID = @CHILD_NODE_ID;

    fetch next from CHILDS into @CHILD_NODE_ID;
  end
  close CHILDS;
  deallocate CHILDS;

  delete from MY_TABLE where NODE_ID = @NODE_ID
END
GO

顺便说一句，您应该在表中为字段PARENT_NODE_ID和NODE_ID添加一个关系(外键)，这样就不能删除没有连接的子节点。