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

Posted in SQL Server | Leave a comment

Service Broker Message Types

Again, no original thoughts on my end here, but this short and sweet article gives a nice high level overview from “SQL Server with Mr. Denny”

Back to Basics Service Broker Message Types

Robert Wallace
No Slogans, Just Results

Posted in Service Broker, SQL Server | Leave a comment

Implementing a custom enumerator for SQL Server TVP

Helpful article by Lenni Lobel.

http://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/

Posted in SQL Server, Table Value Parameter | Leave a comment

How to Size Fill Factor on an Index

Great start and process for determining fill factor by anuchaw.

http://blogs.msdn.com/windchillonsql/archive/2010/04/09/more-about-how-to-size-fill-factor-on-an-index.aspx

Posted in SQL Server | Leave a comment

How to Avoid Some Common Developer and DBA Mistakes

Great article by Brad M. McGehee, Director of DBA Education, Red Gate Software

Read more: http://www.bradmcgehee.com/2010/04/how-to-avoid-some-common-developer-and-dba-mistakes/#ixzz0lYUIKydM

Posted in SQL Server | Tagged | Leave a comment

VSTS 2008, Assembly Versions and Deployment

Microsoft changed the deployment of  assemblies with Visual Studio 2008. When you created a set up package in VS2005 or lower, the assemblies could have “any” version you wanted. When a user executed the setup package, all previous versions of the dll were deleted and the new ones installed.

However, with VSTS 2008 (and on I am assuming), the setup package only UPDATES the dlls that have NEWER versions.

This may not be a big deal for many because of the version numbering of 1.0.*.*. For those that do use a version number, this did have an impact.

Yes, I know about builds and it should have been incremented anyhow, but sometimes I am human 🙂

Robert Wallace
No Slogans, Just Results

Posted in Visual Studio | Tagged | Leave a comment

Installing Custom Windows Service on Win Server 2008R2

To avoid odd “event log” creation errors or window service installs that seem to go nowhere on a window server 2008 R2 machine (prolly others too), always remember to “run as administrator” with a right mouse click.

Spent a good part of the day trying to track down why an event log could not be created. The installation was NOT executing as administrator, even though I am an administrator on the box. I had to explicitly start Command Prompt with “run as”.

Robert Wallace
No Slogans, Just Results

Posted in Windows Server 2008, Windows Service | Tagged | Leave a comment