Post reply

Error: Cannot insert duplicate key row in... a non-unique index?!

  • November 9, 2022 at 8:23 pm

    #4110695

    Thanks for this thread - we were having the exact same problem and this has really helped us get to the bottom of this issue

    • SQL 2016 SP3
    • intermittent "Cannot insert duplicate key row in object 'star.FactBlah' with unique index 'IX_star_FactBlah_Something'. The duplicate key value is (-1, 6533092).
    • Error triggered from an UPDATE statement joining on a non-PK column(s) that have unique constraints on)
    • This index was a single column non-unique index and shows this unexpected second column in the error
    • Intermittence was quite random but happening multiple times a day on an hourly load ETL process across a number of different tables that use the same insert/update/delete pattern
    • Happens during plans with paralellism. We need high parallelism for performance, so dop 1 not an option
    • We had change tracking on the target table. We have current and past other intermittent issues pointing to change tracking bugs with parallel plans.
    • The UPDATE did include the PK column in the update. This was reasonable as we were joining on another set of unique column(s) as previously mentioned
    • it is early days as difficult to repro but removing the PK column from the update however seems to have solved it. If it sticks will have to try doing the update on the PK in a different way after reviewing if and when it is necessary

    Thanks again

  • November 13, 2022 at 9:41 pm

    #4112446

    I still cannot fathom the idea of or the need for updating the PK.

    --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.

    Change is inevitable... Change for the better is not.

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

Viewing 2 posts - 31 through 31 (of 31 total)

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