如何从包含自引用外键的表中删除数据

Question

这是针对一个MS SQL 2005服务器的。我有一个查询，可以删除表中所有孤立的记录。然而，这个表有自引用的外键。我也需要删除它们，但不清楚如何做到。当前脚本删除了所有在其他表中未出现为外键的记录，但没有考虑到其自身表中的自引用外键。该表很简单：

PK, FK, DAta
1, NULL, jibberjab
2, 1, jibberjab2
3, 1, skdfasfa

- jerle78

---

我无法截断数据，因为大部分数据都是好的。 - jerle78

Answer 1

如果您要使用多级结构，可以使用公共表达式来获取您需要删除的ID，并在一条语句中处理孤儿及其所有后代：

    WITH cte AS (
        SELECT pk
        FROM myTable
        WHERE id = 1  --pk to delete
        UNION ALL
        SELECT t.pk
        FROM myTable t
        JOIN cte c
            ON t.fk = c.pk
    )
    DELETE t
    FROM cte c
    JOIN myTable t
        ON c.pk = t.pk

- Aaron Silverman

Answer 2

这可能最简单的方法是分两步进行，首先删除孤立的记录，然后再删除在第一次删除后成为孤儿的引用子项：

DELETE FROM TABLE1
WHERE NOT EXISTS (SELECT 1 FROM TABLE2 WHERE TABLE2.FK = TABLE1.PK)

DELETE FROM TABLE1 
WHERE NOT EXISTS (SELECT 1 FROM TABLE1 B WHERE B.FK = TABLE1.PK)

- Russ

Answer 3

可能你可以将约束配置为在父项删除时被删除：

ALTER TABLE table1 WITH CHECK ADD CONSTRAINT FK_table1_table2 FOREIGN KEY(column1)
REFERENCES table2 (ID) ON DELETE SET NULL -- here
ALTER TABLE table1 CHECK CONSTRAINT FK_table1_table2
GO

- abatishchev

Answer 4

你正在尝试在保持自我引用完整性的同时删除记录。我建议：

1. 删除约束
2. 然后编写查询以使用连接删除相关记录。

创建触发器（用于删除）

CREATE TRIGGER ON ExTable  
 FOR DELETE  
AS  
 IF EXISTS (SELECT * FROM ExTable AS tbl   
     JOIN DELETED AS del ON del.FK=tbl1.PK)  
    DELETE FROM ExTable  
      FROM ExTable ex   JOIN DELETED del ON  
        ex.FK=del.ID

- user3535873