none
INSERT statement conflicted with FOREIGN KEY constraint RRS feed

  • Question

  • 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

    Tuesday, November 21, 2017 8:31 AM

All replies

  • 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


    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

    Tuesday, November 21, 2017 9:19 AM
  • 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

    Tuesday, November 21, 2017 9:38 AM
  • 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


    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

    Tuesday, November 21, 2017 10:06 AM
  • 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? 

    Tuesday, November 21, 2017 10:13 AM
  • 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


    • Edited by Visakh16MVP Tuesday, November 21, 2017 10:34 AM
    Tuesday, November 21, 2017 10:34 AM
  • Dear Visakh,

      Confirmed you both columns are same constraint.  Thanks

    Ming

    Wednesday, November 22, 2017 1:08 AM