locked
Error 2601: Cannot insert duplicate key row in object RRS feed

  • Question

  • We have suddenly started running into errors when attempting to publish to a MDS entity, both from the Excel add-in and the Web application.

    When trying to add a member using the web app we encounter the error "2601: cannot insert duplicate key row in object 'mdm.tbl_2_30_EN' with unique index 'ux_tbl_2_30_EN_Version_ID_Code'. The duplicate key value is (2, 106229)."

    I have queried the table mdm.tbl_2_30_EN and it seems that ID 106229 would be the next key created.  It does not exist yet, so I do not know why this error is occurring.

    Has anybody ran into this problem using MDS before?  Why would this entity suddenly become corrupted somehow?

    Any ideas would be appreciated.

    Thanks.

    Tuesday, September 2, 2014 9:48 PM

Answers

  • You might be having a similar problem, where the index you mentioned was supposed to be a filtered index but inadvertently got modified to be a regular index, which would indicate the duplicate error problem. You may need to figure out if this is the case by looking at the schema history report for the database (right click DB -->reports-->schema history...)

    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 3, 2014 3:17 AM

All replies

  • Solution http://ollysense.blogspot.com/2011_11_01_archive.html


    Satish Kartan http://www.sqlfood.com/

    Tuesday, September 2, 2014 10:18 PM
  • I'm sorry Satish, but that solution does not make much sense to me.  I've read through that blog post a couple times and I do not see the solution. Where exactly is the fix?
    Tuesday, September 2, 2014 10:37 PM
  • Tony - The last line mentions that the WHERE clause of the unique constraint was inadvertently modified. It should be a filtered index (WHERE ([VersionMember_ID] IS NOT NULL))


    Satish Kartan http://www.sqlfood.com/

    Tuesday, September 2, 2014 11:00 PM
  • My error is occurring on a different index though.

    The unique index in my problem is "ux_tbl_2_30_EN_Versoin_ID_Code", while Olly's error was occurring in "ix_tbl_3_38_EN_Version_ID_VersionMember_ID".  

    I'm sorry, but I don't understand how to relate that solution to my problem.  

    Tuesday, September 2, 2014 11:13 PM
  • You might be having a similar problem, where the index you mentioned was supposed to be a filtered index but inadvertently got modified to be a regular index, which would indicate the duplicate error problem. You may need to figure out if this is the case by looking at the schema history report for the database (right click DB -->reports-->schema history...)

    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 3, 2014 3:17 AM
  • Change "Start with". For example 106300.

    Monday, December 11, 2017 7:52 AM