如何删除我要引入的外键失败的行

In a given SQL Server 2008 table I have a disabled foreign key constraint that should be enabled. I cannot enable this constraint because there are rows in this table that conflict with this foreign key. These rows are invalid.

在给定的SQL Server 2008表中,我有一个应该启用的禁用外键约束。我无法启用此约束,因为此表中的行与此外键冲突。这些行无效。

Is there a simple trick that removes data that is conflicting with this constraint?

是否有一个简单的技巧可以删除与此约束冲突的数据?

3 个解决方案

#1

you have to manually remove all the row that doesn't respect the constrain: probably you will need a T-SQL statement to get all the row you need to remove(perhaps using an outer join) then remove them

你必须手动删除所有不遵守约束的行:可能你需要一个T-SQL语句来获取你需要删除的所有行(可能使用外连接)然后删除它们

#2

This would be one way to do it

这将是一种方法

SQL Script

BEGIN TRAN

DELETE FROM Detail    
OUTPUT DELETED.*  -- Verification
FROM   Detail d
       LEFT OUTER JOIN Master m ON m.PK = d.FK
WHERE  m.PK IS NULL

ROLLBACK TRAN -- Change to commit when verified.

#3

You can identify the offending data with this query:

您可以使用此查询识别有问题的数据:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

#1