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

Question

我有一个包含数百万行的 SQL 2005 表，用户日夜不停地访问该表。该表被大约 20 个具有外键约束的其他表引用。我需要定期做的是从该表中删除“Active”字段设置为 false 的所有记录，并且任何子表中都没有引用父记录的其他记录。尝试一次删除每个数据并让它在违反约束的数据上导致 SQL 错误，那么最有效的方法是什么？此外，它不是禁用约束的选项，并且我不能在父表上造成任何长时间的锁定。

Answer 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