20

What can be the use of an external ID field, not marked as unique? Helps in indexing?

I checked the documentation , and found that to upsert records with an external ID, there is no requirement that the external ID be unique. So, if I upsert records using a non-unique external ID, will I not get error saying "Duplicate External IDs" found? If not, how will the upsert pick records that have duplicate values in this external ID field?

Thanks.

2
  • is there a documentation which says this?
    – Dharshni
    Feb 7, 2014 at 12:11
  • To my knowledge, there is not benefit other than trying to mark a field as External ID so that it is indexed and searchable. But if you want it to truly uniquely identify the record then you need to also mark the field as Unique.
    – Doug Ayers
    Apr 16, 2016 at 20:37

3 Answers 3

15

Unfortunately you are right, using external IDs can lead to multiple matches during upsert operations.. taken from the documentation on Upserting examples:

If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated.

So in answer to your question, yes you will get an error! Frustrating as it is.

Edit: This would need checking, but there is a specific exception type DmlException, which I imagine is what would be thrown, and you could (and probably should anyway) look to catch after an upsert call

try
{
    upsert yourList;
}
catch (DmlException e)
{
    // Report your error and/or try to re-handle the list?
}

Edit 2(!): Don't forget of course as well you can set your external ID to have unique (and case sensitive, and required) properties, which I would personally recommend if you ever intend on using the field in upsert commands.

Image

2
  • /@sathya, do you know of a scenario where a duplicate EID would be needed/useful?
    – Bartley
    Feb 7, 2014 at 12:48
  • 10
    The problem with the unique attribute is that it can't be turned on if it would be violated by existing non-null values. This is particularly a problem with data that already exists in the database that is marked as "external id" sometime after its creation. In that case, you have to first fix up the values so they are unique before enabling the unique attribute. External ID fields should always be marked as unique, but for practical purposes, sometimes it can't be because of dirty data.
    – sfdcfox
    Feb 7, 2014 at 18:06
2

We have a use case for this. We have some external data sources that are not very well structured. We need to import "jobs" comprising of areas and products within those areas into Salesforce and ideally maintain the link between product and area records. The only reference the external data has to link a product to an area is the area name but in some cases, a single job has multiple areas with the same name.
By setting up area name + job id as an external id (non-unique), we have a key that is usually good enough to hook up the products. If it fails due to duplicate external ids, we will just set the lookup to null and upsert again, because having all the data in Salesforce is still useful in our case, even if we cannot match due to the duplicate issue.

1
  • That's in interesting use case. I guess it's a matter of "close enough is good enough". In most cases the external Id is unique, but let's not enforce it on the parent object, letting the child object insertion work in most cases.
    – Nick C
    Aug 24, 2016 at 6:29
0

Making a field an external id causes an index to be created on the field. If you are getting a "Non-selective query" QueryException when using a field in the WHERE clause, you may be able to resolve it by marking the field as an external id even if you want to use it as an actual external id (or can't due to duplicate values).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .