How to delete rows that fail a foreign key i want to introduce

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.

Is there a simple trick that removes data that is conflicting with this constraint开发者_运维百科?

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.

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

You can identify the offending data with this query:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS