删除丢失父母的孩子记录

Question

我正在尝试创建一个SQL查询来删除父记录时已删除的子记录。
问题是，子节点和父节点都存储在同一个表中。
下面是(简化的)数据模型:

ThingTable

    ID  |  Name  | ParentID
   1000 | Thing1 |   NULL
   1001 | Thing2 |   1000
   1002 | Thing3 |   1000
   1003 | Thing4 |   1000
   1004 | Thing5 |   1003

ChildThingTable

    ID  |  Color  
   1001 |  Blue
   1002 |  Black
   1003 |  Green
   1004 |  Red  

假设ID 1000(父节点)被删除，我需要从ChildThingTable和ThingTable中删除相应的记录。

唯一的限制是不能使用触发器或以任何方式改变底层数据库结构。

这是我的伪代码，我已经解决了，但我有困难把它翻译成SQL:

1. 根据ID和ParentID从ChildThingTable中删除没有父节点的匹配记录。
2. 从没有父记录的ThingTable中删除。

任何帮助都将非常感激!

Answer 1

您可以使用公共表表达式递归

-- Begin Create Test Data
SET NOCOUNT ON 
CREATE TABLE #ThingTable (
ID INT NOT NULL,
[Name] VARCHAR(255) NOT NULL,
[ParentID] INT NULL
)

CREATE TABLE #ChildThingTable (
ID INT NOT NULL,
[Color] VARCHAR(255) NOT NULL,
)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1000,'Thing1',NULL)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1001,'Thing2',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1002,'Thing3',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1003,'Thing4',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1004,'Thing5',1003)
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1001 , 'Blue')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1002 , 'Black')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1003 , 'Green')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1004 , 'Red')
SET NOCOUNT OFF
GO
-- End Create Test Data
-- This is a batch, but could easily be a stored procedure.  
DECLARE @InputID INT 
SET @InputID = 1000;
SET NOCOUNT ON
DECLARE @Temp TABLE(ID INT NOT NULL);
WITH ThingCTE (ID, ParentID, [Level])
AS
(
SELECT tt1.ID, tt1.ParentID, 1 AS [Level]
FROM #ThingTable tt1
WHERE tt1.ID = @InputID
UNION ALL
SELECT tt2.ID, tt2.ParentID, tc1.[Level]+1
FROM #ThingTable tt2
JOIN ThingCTE tc1 ON (tt2.ParentID = tc1.ID)
)
INSERT INTO @Temp
        ( ID )
SELECT ID
FROM ThingCTE
SET NOCOUNT OFF
DELETE ctt
-- Output is for debug purposes, should be commented out in production.
OUTPUT Deleted.* 
FROM #ChildThingTable ctt
JOIN @Temp t ON (ctt.ID = t.ID);
DELETE tt 
-- Output is for debug purposes, should be commented out in production.
OUTPUT Deleted.* 
FROM #ThingTable tt
JOIN @Temp t ON (tt.ID = t.ID)
DROP TABLE #ChildThingTable;
DROP TABLE #ThingTable;

Answer 2

添加约束是否构成'以任何方式改变底层数据库结构'

因为我会考虑使用一个带有级联Delete的约束，这将要求父Id列是Id表的外键，然后在删除主键时添加约束，匹配的外键也将被删除。

ALTER TABLE dbo.T2
ADD CONSTRAINT FK_T1_T2_Cascade
FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

也有递归查询可以做类似的事情，但我将从这个开始。

那么像这样的东西应该可以工作http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

DELETE
FROM thingtable START WITH ID = 1000
CONNECT BY PRIOR ID = ParentID;

Answer 3

如果我对你的问题理解正确的话，这个程序应该可以…

CREATE PROCEDURE DeleteThing 
    @IdToDelete INT
AS
BEGIN
    DELETE ChildThingTable
    WHERE ID IN ( SELECT ID FROM ThingTable WHERE ParentId = @IdToDelete );
    DELETE ChildThingTable
    WHERE ID = @IdToDelete;
    DELETE ThingTable
    WHERE ParentID = @IdToDelete;
    DELETE ThingTable
    WHERE ID = @IdToDelete;
END;

您可能希望将此封装在事务中，以便整个操作成功或失败。

正如其他人提到的，这正是您应该使用引用完整性结构(外键)的原因。它们的存在是为了防止无效数据。

Answer 4

尝试用递归cte一直爬到根目录，这样每一项都不仅显示了ParentID，还显示了父目录的ParentID等等。

CREATE TABLE Things (
  ID INT NOT NULL,
  [Name] VARCHAR(255) NOT NULL,
  [ParentID] INT NULL
);
INSERT INTO Things (ID,[Name],ParentID)
select 1000,'Thing1',NULL union all
select 1001,'Thing2',1000 union all
select 1002,'Thing3',1000 union all
select 1003,'Thing4',1002 union all
select 1004,'Thing5',1003;

注意，我在你的数据中添加了一个级别，以显示我们可以一直到顶部，所以Thing5的父级是Thing4，它的父级是Thing3，它的父级是Thing1。

with
Parents( ID, Name, ParentID, GrandParentID )
as(
  select t1.ID, t1.Name, t2.ID, t2.ParentID
    from Things t1
  left join Things t2
      on t2.id = t1.ParentID
  union all
  select t1.ID, t1.Name, p.ParentID, p.GrandParentID
    from Things t1
    join Parents p
      on p.ID = t1.ParentID
   where p.ParentID is not null or p.GrandParentID is not null
)
select ID, Name, ParentID
from Parents
order by ID, ParentID desc;

产生如下输出Fiddle:

ID   NAME   PARENTID
==== ====== ========
1000 Thing1 (null)
1001 Thing2 1000
1002 Thing3 1000
1003 Thing4 1002
1003 Thing4 1000
1004 Thing5 1003
1004 Thing5 1002
1004 Thing5 1000

所以如果你删除了Thing3 (ID 1002)，那么也删除上面ParentID列中所有带有1002的内容(Thing4和Thing5)。

从子表中删除孤儿是很简单的:做一个左连接回到thing表，并删除所有没有找到匹配的

Answer 5

我通常在父记录被删除后从子表中删除孤儿。

DELETE T FROM ThingTable T WHERE NOT EXISTS (SELECT NULL FROM ParentTable WHERE Id=T.Id)
DELETE C FROM ChildThingTable C WHERE NOT EXISTS (SELECT NULL FROM ThingTable WHERE Id=C.Id)