删除所有没有外键约束的记录

一尘不染

删除所有没有外键约束的记录

sql

我有一个SQL
2005表，其中有数以百万计的行，整夜都在被用户击中。大约有20个具有外键约束的其他表引用该表。我需要定期执行的操作是从此表中删除所有记录，其中“活动”字段设置为false，并且在任何引用父记录的子表中都没有其他记录。什么是最有效的方法呢？一次尝试删除每个错误，让它在违反约束的错误上引起SQL错误吗？同样，禁用约束不是一种选择，并且我不能在任何可观的时间内导致对父表的锁定。

阅读 155

2021-05-23

共1个答案

一尘不染

如果未链接的非活动行不太可能被链接，则可以运行（或什至根据外键元数据动态构建）：

SELECT k.*
FROM k WITH(NOLOCK)
WHERE k.Active = 0
AND NOT EXISTS (SELECT * FROM f_1 WITH(NOLOCK) WHERE f_1.fk = k.pk)
AND NOT EXISTS (SELECT * FROM f_2 WITH(NOLOCK) WHERE f_2.fk = k.pk)
...
AND NOT EXISTS (SELECT * FROM f_n WITH(NOLOCK) WHERE f_n.fk = k.pk)

您可以轻松地将其转换为DELETE。但是，大的删除操作可能会持有很多锁，因此您可能希望将其放在表中，然后分批删除-除非链接了记录，否则批处理应该不会失败。

为了使此方法有效，您确实需要在相关表的FK列上具有索引。

您也可以使用左联接来执行此操作，但是（有时）您必须（有时）使用DISTINCT或GROUP
BY进行重复数据删除，并且执行计划通常实际上并不更好，并且不利于代码生成：

SELECT k.*
FROM k WITH(NOLOCK)
LEFT JOIN f_1 WITH(NOLOCK) ON f_1.fk = k.pk
LEFT JOIN f_2 WITH(NOLOCK) ON f_2.fk = k.pk
...
LEFT JOIN f_n WITH(NOLOCK) ON f_n.fk = k.pk
WHERE k.Active = 0
    AND f_1.fk IS NULL
    AND f_2.fk IS NULL
    ...
    AND f_n.fk IS NULL

2021-05-23