CODEWITHSUNDEEP
sqlsql-serverforeign-keysconstraintsconflict
sprocket12
sprocket12

Reputation: 5488

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 :

On SQL Server 2008 R2

Any ideas what I could try?

Upvotes: 3

Views: 5144

Answers (3)

Taher Chtaywi
Taher Chtaywi

Reputation: 41

I had the same issue I clear the data of the tables where i want to edit the content and add a new foreign key constraint and retry. It works for me. I hope it help you.

Upvotes: 0

Mike Gledhill
Mike Gledhill

Reputation: 29213

This one was baffling me aswell, and then the penny dropped.

I was trying to create a Foreign Key using "Database Diagrams" in SQL Server 2012, but it refused to let me as it claimed to clash with the foreign key I was trying to create. Huh ?

But, I had accepted the defaults to "Enforce foreign key constraint". But I already had data in the two tables I was attempting to create a foreign key for, and it was breaking the foreign key rule I was trying to make, so SQL Server was rejecting the new key.

The solution (in this particular case) was to change "Enforce foreign key constraint" to "No", or at least until I had cleaned up my data.

Hope this helps.

Upvotes: 0

Bulat
Bulat

Reputation: 6979

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.

Upvotes: 7

Related Questions