options.match and contacts in the recycle bin (is_deleted=1)

[johanv]

Hi all,

Using 'options.match', you can update an existing contact based on its id, as described on: http://wiki.civicrm.org/confluence/display/CRMDOC/API+changes#APIchanges-4.4.0:%27create%27and%27replace%27actionsaccept%27options.match%27or%27options.match-mandatory%27

For example:

Code: [Select]

$result = civicrm_api('contact', 'create', array(
  'version' => 3,
  'contact_type' => 'Individual',
  'first_name' => 'Joe',
  'last_name' => 'Schmoe',
  'external_identifier' => '1234',
  'options' => array(
    'match' => 'external_identifier',
  ),
));

If a contact with the external identifier 1234 already exists, its name is overwritten. If not, this creates a new contact.

That is, except when a contact with external identifier 1234 is in the 'recycle bin' (is_deleted is set to 1). In that case, this API call fails, with error message 'DB error: already exists.'

Is this expected behavior, or is this a bug?

[Eileen]

I'ts likely a clash on the external ID I guess - since it's obviously choosing not to merge with a deleted contact. I'm not 100% sure the best behaviour  (better error or 'steal' the identifier)

[xavier]

Or restore the is_deleted contact and update it?

X+

[joanne]

My vote would be for a better error message. 

If the contact is in trash it could be because it was a duplicate of another contact.  In that case either stealing external id or restoring the deleted contact will just introduce a duplicate again.

[xavier]

Good point.

What about changing the constraint in the db? instead of having a unique external identifier, setting a unique (is_deleted, external_identifier) so you could have both a deleted contact and a normal one with the same external id.

It will be still a problem if you try to delete a contact with an external id when you already have a deleted contact, but that's the edge case of the edge case, probably not worthwhile sweating it.

X+

[Eileen]

It's an edge case an end user could hit - so probably more serious that the problem we are trying to solve

[Insert feeble joke which takes concept about cure being worse than cause but uses it in creative, bizarre or obscure context]

[joanne]

I agree with Eileen.

Most of our is_deleted = 1  contacts  are there because they are duplicates.  I had to do an import of some data matching on external Ids and it was best to get an 'already exists' error message and deal with those contacts manually.

[xavier]

agree with Joanne and Eileen, but quite disappointed that the later didn't manage to find a feeble joke

[totten]

It sounds like the intention is to impose a constraint like this:

There may only be one active record with a given external_identifier. There may be any number of inactive records.

MySQL's constraint system doesn't seem powerful enough to express this directly, but one could probably use a PRE INSERT / PRE UPDATE trigger, e.g.

http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/

[joanne]

Seems I didn't express myself clearly enough.

I would like to see is the current system remain ie:

There may only be one record with a given external_identifier. That record may be active or it may be inactive.

My thoughts are that a contact is trashed for a reason, so the system shouldn't automatically create an active record with the external id of a trashed record, nor should it restore a trashed record; each such case should be reviewed individually by a person.

The original question in this topic was:

Is this expected behaviour, or is this a bug?

I would say it is expected behaviour, perhaps not the behaviour that @johanv would prefer, but definitely not a bug.

I don't know if the current message of 'DB error: already exists.' is inadequate. Perhaps people would not think to search in trash and so may not be able to find the existing trashed record.

If that is a concern than perhaps the error message should be: 'DB error: already exists in trash.'