DB error when duplicate contact has an external identifier

[DanilaD]

I tried to merge two contacts - the duplicate was created during import, and has an external identifier set; the contact to be left was sitting in database.

To replicate on the demo site, I've created a contact John Jonathan. Then I imported the attached file, test_case_for_ExternalID.txt, "No duplicate checking".

Then I created a simple fuzzy rule, requiring first 3 letters of last name and first name to be equal (to catch the duplicates easily).

Then I click the merge link for John, making sure the contact with ExternalID is on left side (see the screenshot).

After this I get a DB error:

Code: [Select]

Database Error Code: Duplicate entry '123321' for key 2, 1062
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => UPDATE  civicrm_contact  SET contact_type = 'Individual' , external_identifier = '123321' , display_name = 'John Jonathan'   WHERE (  civicrm_contact.id = 115 )   [nativecode=1062 ** Duplicate entry '123321' for key 2]
    [type] => DB_Error
    [user_info] => UPDATE  civicrm_contact  SET contact_type = 'Individual' , external_identifier = '123321' , display_name = 'John Jonathan'   WHERE (  civicrm_contact.id = 115 )   [nativecode=1062 ** Duplicate entry '123321' for key 2]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="UPDATE  civicrm_contact  SET contact_type = 'Individual' , external_identifier = '123321' , display_name = 'John Jonathan'   WHERE (  civicrm_contact.id = 115 )   [nativecode=1062 ** Duplicate entry '123321' for key 2]"]
)

Suggestion: for external IDs (and, probably, some other fields that cannot repeat, if there are such), first delete the field value in 'duplicate' contact, save it to some variable, and then empty it, so that the value can be assigned to the contact whish is left.

Regards,
Danila[/code]

[DanilaD]

Could this be because the deleted contact is still in the database? I cannot set the external ID of Jonathan to 123321, it tells

Please correct the following errors in the form fields below:
    * External ID already exists in Database.

[xavier]

Are you still using a 2.2 civicrm  as per your profile ?

As of 3.2, deleted contacts are still in the db, that might be what triggers your problem, if you are on 2.2, no idea.

X+

[DanilaD]

Hello, Xavier.

I've tested this on the current demo site, http://drupal.demo.civicrm.org/, so it is 3.2.

Danila

[Donald Lobo]

hmm, i do think external identifier should be unique across the entire DB, irrespective of whether a contact has been deleted or not.

i dont think we should "tweak" around with "thrashed" contacts to get around the DB requirements. There is always the "permanent" delete option

lobo

[DanilaD]

Thank you, Lobo.

I think this is a problem with my importing workflow, not with CiviCRM.

Regards,
Danila