这是针对一个MS SQL 2005服务器的。我有一个查询,可以删除表中所有孤立的记录。然而,这个表有自引用的外键。我也需要删除它们,但不清楚如何做到。当前脚本删除了所有在其他表中未出现为外键的记录,但没有考虑到其自身表中的自引用外键。该表很简单:
PK, FK, DAta
1, NULL, jibberjab
2, 1, jibberjab2
3, 1, skdfasfa
这是针对一个MS SQL 2005服务器的。我有一个查询,可以删除表中所有孤立的记录。然而,这个表有自引用的外键。我也需要删除它们,但不清楚如何做到。当前脚本删除了所有在其他表中未出现为外键的记录,但没有考虑到其自身表中的自引用外键。该表很简单:
PK, FK, DAta
1, NULL, jibberjab
2, 1, jibberjab2
3, 1, skdfasfa
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
这可能最简单的方法是分两步进行,首先删除孤立的记录,然后再删除在第一次删除后成为孤儿的引用子项:
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)
可能你可以将约束配置为在父项删除时被删除:
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
创建触发器(用于删除)
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