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