Takeaway: Solution is “ORDER BY 1”
This is a very deceptive error and spent a few hours working it out. Hopefully this will save some of you some pain.
While maintaining and improving performance on some stored procedures, I came across this weird FK issue. I never actually changed the code of the failed statement itself. The proc was using a #temp table to which I added a primary key for performance reasons.
However, when it came time to insert the #temp table data into the real table, I received and error that looked like this:
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_XXX_YYY”. The conflict occurred in database “MyDatabase”, table “dbo.MyTable”, column ‘MyColumn’.
However, the original procedure did not have this error. I went through the normal debugging steps. I checked to make sure the primary table data was there. I checked for nulls. I got a distinct list of key values. I saved the data off and then ran the same insert statement outside of the proc…and it worked!!! Now I was very confused. The data failed in the proc and but did not outside the proc. No one was touching any data nor was any data getting loaded in between runs/tests.
So I googled it and found this link.
To cut to the chase, here is the important part:
Update: There is a bug in microsoft sql server’s Hash Map join plan when it comes to # based temp tables.
To suppress this error(workaround given by Microsoft team): Include order by 1 clause – this will change the join plan.
INSERT INTO MAIN_TABLE (DOC_ID_FK_TO_SOME_OTHER_TABLE, ANY_COLUMN)
SELECT DOC_ID_FK_TO_SOME_OTHER_TABLE, ANY_COLUMN
FROM #TEMP_TABLE — This will sometimes throw FK violation which is not a –valid violation
ORDER BY 1 — This suppresses the error
I put the “ORDER BY 1” on my insert statement and it worked. Thanks for the original poster who gave us this great nugget of info.
No Slogans, Just Results