FK Constraint error but primary table data exists

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.

http://www.sqlservercentral.com/Forums/Topic771369-149-1.aspx

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.

For eg.
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.

Robert Wallace
No Slogans, Just Results

Advertisements

About RWallace Consulting, LLC

Since 2002, RWallace Consulting, LLC has been developing software across a myriad of industries. Right now, we specialize in Microsoft .NET technologies.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s