Cannot insert duplicate key row in object

  • chris.o.smith

    Default port

    Points: 1488

    Hi,
    I'm on SQL Server 2014 enterprise edition.  I'm running into the following error when attempting to run a MERGE statement: "Cannot insert duplicate key row in object 'TABLENAME' with unique index 'INDEX_NAME'. The duplicate key value is (###, ###).".  The strange thing is that the index that is referenced ("INDEX_NAME") is not a unique index, and it doesn't have 2 key columns as indicated in the error message.  The definition of INDEX_NAME is as follows: 
    CREATE NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME] ([FK_Column1] ASC)INCLUDE ([FK_Column2])

    In addition, if I rebuild the index mentioned in the error message and then re-run the same exact MERGE statement, it succeeds.  I'm lost as to what is going on here.  The only thing that I can see that is a little different with this table is that the PK column is set up as a unique index, and the clustered index is on a different column (this design is out of my control, so its not an option to change it and see if it resolves the issue).

    Any ideas on what is happening here?

  • Joe Torre

    SSChampion

    Points: 10238

    Please check this out. You may need to apply SQL Server 2014 Cumulative Update 4 or above

  • chris.o.smith

    Default port

    Points: 1488

    Thanks - that certainly sounds a lot like my issue, but we're currently on SQL Server 2014 SP2 CU4 (12.0.5540.0)...

  • rajborntodare

    Hall of Fame

    Points: 3576

    Hi Chris,

    Just to give an Idea, did you check the original column of the FK column ,  if it is making any inserts there.
    Also check the properties of the index for option Ignore Duplicate values ;p

  • chris.o.smith

    Default port

    Points: 1488

    Thanks, yes, we've thoroughly checked all of this and no luck.  If we drop and recreate the same exact index, and then run the same exact MERGE statement with the same exact underlying data, it works.

    It also works if we change the structure of the table so that the clustered index is on the PK column.  So, to resolve my issue, I'm pushing to have the structure of the table changed permanently so that the clustered index is on the PK.  This seems like an MS bug to me (sounds very similar to the link that Joe Torre posted above).

  • Jeff Moden

    SSC Guru

    Points: 994663

    Sounds like a lot of work and problems with MERGE have and (apparently) continue with it.  I just do good ol' fashioned "Upserts" and call the job done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply