SQL Question

Unable to add foreign key constraint due to obscure conflict

Trying to run :

ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_ScenarioResult]
FOREIGN KEY ([ScenarioResultID]) REFERENCES [dbo].[ScenarioResult] ([ScenarioResultID]) ON DELETE CASCADE


Getting this error :

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Table1_ScenarioResult". The conflict occurred in database "8362", table "dbo.ScenarioResult", column 'ScenarioResultID'.


I have checked :


  • Constraint does not already exist, and no other exists on same column

  • The values in the column match in both tables

  • Types of columns are the same

  • Tried a different name, also fails



On
SQL Server 2008 R2


Any ideas what I could try?

Answer

In theory this might work:

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [FK_Table1_ScenarioResult] 
FOREIGN KEY ([ScenarioResultID]) REFERENCES [dbo].[ScenarioResult] ([ScenarioResultID]) 
ON DELETE CASCADE

Not sure how you checked for integrity of existing values, but it should be:

SELECT COUNT(*) as Orphans FROM [dbo].[Table1] t 
WHERE NOT EXISTS 
  (SELECT * FROM [dbo].[ScenarioResult] WHERE ScenarioResultID = t.ScenarioResultID)

If "Orphans" is greater then zero you need to clean the data before adding a constraint.