INSERT statement conflicted with FOREIGN KEY constraint

Category: sql server distributed

Question

Ah_Ming on Tue, 21 Nov 2017 08:31:11


Background:

(1) generate a trace file by SQL profiler 2005

(2) replay a trace file by SQL profiler 2016

(3) Found many errors about "INSERT statement conflicted with FOREIGN KEY constraint"

[Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TEST1". The conflict occurred in database "TESTDB", table "dbo.TEST2", column 'TEST2_No'. (State 23000) (Code 547)
[Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. (State 01000) (Code 3621)

(4) Check the database and confirm no conflict of foreign key in SQL server 2017

Question:

Is it a compatibility between 2005 and 2016?  Please advise.  Thanks!

Ming

Replies

Visakh16 on Tue, 21 Nov 2017 09:19:54


Nope

FOREIGN KEY has nothing to do with compatibility

What you're seeing is result of difference in data between the two servers

i.e There are some reference data in 2005 which is missing in your 2016 db.

The insert includes some reference data which is missing in your 2016.

Inorder for insert to work you need to copy the missing data from your 2005 db to 2016

To understand which table what column its referring to, you need to look at definition of FOREIGN KEY CONSTRAINT with the name FK_TEST1

Ah_Ming on Tue, 21 Nov 2017 09:38:06


Dear Visakh,

  Thanks for your hints.  It means the replay data should match with the time of capture data.  Correct?  One more question, it is for my interest.  Replay will not affect the integrity of data.  How replay to simulate the bulk of SQL statements?

Ming

Visakh16 on Tue, 21 Nov 2017 10:06:06


Dear Visakh,

  Thanks for your hints.  It means the replay data should match with the time of capture data.  Correct?  One more question, it is for my interest.  Replay will not affect the integrity of data.  How replay to simulate the bulk of SQL statements?

Ming

Yes

Also reference data should exist in the server where you're replaying this corresponding to source

Ah_Ming on Tue, 21 Nov 2017 10:13:31


I check there is no difference in table structures/definition between 2 servers.  All the data is restored by the production server.  Maybe it is a difference in data level.  Will it cause this error? 

Visakh16 on Tue, 21 Nov 2017 10:34:43


I check there is no difference in table structures/definition between 2 servers.  All the data is restored by the production server.  Maybe it is a difference in data level.  Will it cause this error? 

There is only one case in which it can occur

You're inserting a value in the column referred in foreign key column which doesnt exist in its parent table linked by the constraint

It may be that the parent table had an identity column to which this column was linked and whilst creating the data in your server, the identity values got reseeded/regenerated causing the references to fail.


Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page


Ah_Ming on Wed, 22 Nov 2017 01:08:23


Dear Visakh,

  Confirmed you both columns are same constraint.  Thanks

Ming