删除没有外键约束的所有记录 [英] Delete all records that have no foreign key constraints

问题描述

我有一个SQL 2005表，其中有数百万行，被用户一天天晚上击中。此表由20个左右引用具有外键约束的其他表。我需要定期做的是从该表中删除所有记录，其中活动字段设置为false，并且在任何引用父记录的子表中没有其他记录。什么是最有效的方法做到这一点，试图一次删除每一个，让它导致SQL错误违反约束的错误？它也不是禁用约束的选项，我不能导致父表上的锁任何大量的时间。

解决方案

如果不链接的非活动行不可能链接，您可以运行（甚至动态构建，对外键元数据）：

  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）
  pre> 
 
 
你可以很容易地把它变成一个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 
  
 
I have a SQL 2005 table with millions of rows in it that is being hit by users all day and night. This table is referenced by 20 or so other tables that have foreign key constraints. What I am needing to do on a regular basis is delete all records from this table where the "Active" field is set to false AND there are no other records in any of the child tables that reference the parent record. What is the most efficient way of doing this short of trying to delete each one at a time and letting it cause SQL errors on the ones that violate constraints?  Also it is not an option to disable the constraints and I cannot cause locks on the parent table for any significant amount of time. 
解决方案
If it's not likely that inactive rows which are not linked will become linked, you can run (or even dynamically build, based on the foreign key metadata):
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)
And you can turn it into a DELETE pretty easily.  But a large delete could hold a lot of locks, so you might want to put this in a table and then delete in batches - a batch shouldn't fail unless a record got linked.

For this to be efficient, you really need to have indexes on the FK columns in the related tables.

You can also do this with left joins, but then you (sometimes) have to de-dupe with a DISTINCT or GROUP BY and the execution plan isn't really usually any better and it's not as conducive to code-generation:
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


                        
这篇关于删除没有外键约束的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！