DUPLICATE_VALUE error - Composite REST API by External Id

Question

I'm trying to upsert a record using an external Id with composite API.

my external Id is (External ID) (Unique Case Insensitive) - to be precise - I created this field only with the external Id checkbox checked, and after some time I turned it also to be unique.

When I'm trying to update a value that already exists in my system using upsert - (even if it only one record like the JSON below) I'm getting an error :

Duplicate value found: customObject__c duplicates value on record with id: xxxxxxxx

Which of course I have only one record with that current external Id.

After some investigation, I found out that if I'm creating a new record Its Inserted or updated as expected when using the same call.

But when I'm dealing with a record that was in my DB before I turned the external Id field to be unique, I'm getting this above error.

What I'm doing :

https://xxx.salesforce.com/services/data/v50.0/composite/sobjects/customObject__c/CustomExternalIdField__c

{
    "allOrNone": false,
    "records": [{
        "CustomExternalIdField__c":"503",
        "field1__c":"RS",
        "field2__c":"PN",
        "field3__c":"PEN",
        "Name":"Name",
        "field4__c":"RE",
        "attributes": {
            "type": "customObject__c"
        }

    }]
}

P.S

I'm doing this API call on 5 different Objects which all of them have an externalId that changed to be also unique over time - and Its working as excepted.

Any Ideas please?

Answer 1

In case it helps anyone who has the same problem: I had a similiar situation in my org today, and I found out that the object has a before insert trigger which changes the value of the external id field.

I was doing a Patch rest api with "external_id__c" = "example1", and in the before insert trigger, apex changed the value external_id__c = "example2". The first time the REST PATCH worked, inserting the record. But in the following calls what happens is: REST searches for "external_id__c" = "example1" for update >> it does not find any match >> it tries to insert the record >> before insert trigger changes the value external_id__c = "example2" >> rest returns ERROR Duplicate value found: "customObject__c duplicates value on record with id: xxxxxxxx".

Answer 2

I faced a similar issue. This was happening for us because thesource system was iniating the creation and then due automation, initiating an update directly after. The original requests transaction had not completed. Salesforce new that a record was being created with the external id from the update but that the records was not completed yet and therefore blocked the update.