I have a T-SQL as follows: Insert Into SomeTable (col1,col2,col3) Select col1,col2,col3 From SouceTable with (NoLock) Where <somecondition>
There is a FK constraint from col3 in SomeTable to col3 in the SourceTable.Server 1 : IA 64 (sql 2005 enterprise edition 64 bit)It fails with:The INSERT statement conflicted with the FOREIGN KEY constraint "cnsomething". The conflict occurred in database "YourDB", table "dbo.SourceTable", column 'Col3'.
Server 2: (Same config as above) (DR server)NO error.Server 3: 32 bit processor. DB restored from Server 1 above.Same error as in Server 1.SQL Server version is same across all servers.Removing the WITH (Nolock) hint works everywhere without errors.Its understandable that it fails with the NOLOCK hint because there is a chance someone could have deleted/updated col3 which might violate the FK constraint. So SQL Server is being over cautious from the beginning. These errors did not occur when the DB was in 2000, a couple of weeks back. So it looks like the change was made in 2005.What's weird is the error is not consistent across the 2005 editions.Server 1 and 2 have exact same configurations (one is the DR for the other). But the query works in Server 2 and not in 1.Can anyone see if I am overlooking something here...?Thanks,Don
Weird constraint error
Posted - 2008-01-31 : 20:13:34