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

• brett@zerozero.net

  SSC Veteran

  Points: 245

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004600

  More actions

  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)