locked
Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object. RRS feed

  • Question

  • Hi,

    I have a custom entity "TT" which has N:1 relationship with Account (Relationship Behavior - Delete: Cascade All) & N:N relationship with Contact.

    When you create an account record "A1" with a primary contact "C1" and primary contact associated to "A1". Also, create a "T1" record (TT entity) that has C1 contact in the sub grid. I am NOT able to delete the Account record when I have contact sub grid populated. If Sub grid is not populated, I have no problem deleting the Account record.

    I get "Generic SQl Error" error message. I ran the Profiler and found the following error. 

    Violation of PRIMARY KEY constraint 'PK__fn_Colle__6056C81940339838'. Cannot insert duplicate key in object 'dbo.@t'. The duplicate key value is (10046, 13bd5815-074b-e311-b4f6-782bcb6d0c4e, 2).
      
    We are on UR 14. Any suggestions or recommendation to resolve this issue?

    Thanks!


    Monday, November 11, 2013 10:24 PM

Answers

  • I think the underlying problem is that the cascade relationships mean than CRM identifies a record (either the TT record, or the N:N entity) to be deleted via more than one route.

    First of all I'd determine which entity this applies to - it will be the one with ObjectTypeCode = 10046; with CRM OnPremise, I'd find this with the following SQL query

    Select name from entityasifpublished where objecttypecode = 10046
    I think the only solution will be to not have a Delete Cascade behaviour, either on the Account:TT relationship, or maybe the Account:contact relationship. If you need to cascade the deletes, you could di this via a plugin 


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Anna' Tuesday, November 19, 2013 12:40 AM
    Tuesday, November 12, 2013 4:43 PM
    Moderator

All replies

  • I think the underlying problem is that the cascade relationships mean than CRM identifies a record (either the TT record, or the N:N entity) to be deleted via more than one route.

    First of all I'd determine which entity this applies to - it will be the one with ObjectTypeCode = 10046; with CRM OnPremise, I'd find this with the following SQL query

    Select name from entityasifpublished where objecttypecode = 10046
    I think the only solution will be to not have a Delete Cascade behaviour, either on the Account:TT relationship, or maybe the Account:contact relationship. If you need to cascade the deletes, you could di this via a plugin 


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Anna' Tuesday, November 19, 2013 12:40 AM
    Tuesday, November 12, 2013 4:43 PM
    Moderator
  • Thank you for the reply! The entity with objecttypecode=10046 is the table storing N:N relationship between Contact and TT entity.

    With the detail SQL profiling, I found that CRM inserts all the Primary key of the entities (based on the relationship behaviour) into the temp variable before deletion.

    In my case, it inserts the

    (Account, AccountID)  - Account I am trying to delete

    (Contact, Contactid) - Contacts associated to the Account (Cascade behaviour)

    (TT, TTid) - TT record 

    Since TT entity has N:N relationship with Contact record, it tries to insert Contactid from N:N relatinship table to the temp table, but that contactid already exists in temp variable. And that's how it throws an error.

    "I think the only solution will be to not have a Delete Cascade behaviour, either on the Account:TT relationship, or maybe the Account:contact relationship. If you need to cascade the deletes, you could di this via a plugin" 

    I removed the "Delete: Cascade All" relatinship behaviour between Account:TT relationship and it doesn't throw an error leaving the orphan TT record. 


    Tuesday, November 19, 2013 12:39 AM
  • Hi There,

    I am using online version of CRM 2016 and I am getting the same error. 

    Constraint "cndx_PrimaryKey_SavedQuery". Cannot insert duplicate key in object 'dbo.savedQueryBase'

    Could you please help me fix this? 

    Thank you,

    Thursday, December 29, 2016 4:41 AM