Change a formula field and get Data Uniqueness Violation - The proposed data changes would violate a uniqueness constraint

Question

Given

• Existing custom field Asset.SerialNumber (OOTB - Text)
• New custom field Asset.SerialNumber_bis__c (Text255, External Id non-unique, deterministic encrypted case insensitive)
• Existing custom formula field Case.SerialNumber__c that is defined as Asset__r.SerialNumber

When

• use UI field Editor and change the formula in Case.SerialNumber__c
• FROM: Asset__r.SerialNumber
• TO: Asset__r.SerialNumber_bis__c

RESULT

Data Uniqueness Violation - The proposed data changes would violate a uniqueness constraint. upon attempting to save the changed formula field

How do I get around this problem? The newly-referenced field isn't defined to be unique.

Answer 1

Had the same problem. Here is what helped: Change the formula field so it would not reference any fields. I changed it to "test". Save it. Then change it to what was initially required.

Answer 2

So, I have no idea why this is happening but this is the workaround:

On the object with the formula field ...

• Define a new field to be exactly what you were trying to change the old field to. In my situation, that meant defining a new field on Case:

  Case.SerialNumber_bis__c with formula expression Asset__r.SerialNumber_bis__c

• Save the new field

• Hide the old field, perhaps also changing its label
• If you are lucky enough (no Apex/VF references), you might be able to delete the original field, and rename the new field (label and api name) back to the original field's name. This will deploy successfully with Metadata API-based tools.

Notes

• An option you might try - Changing the encryption method on Asset.SerialNumber_bis__c does not work because External Ids must be deterministically-encrypted
• Another option you might try is to remove the External Id from Asset.SerialNumber_bis__c but then you lose the index and your queries won't be selective. Asking for a custom index is a no-go because SFDC can't create custom indices on encrypted text fields - Catch-22!
• A final option you might try is to directly encrypt Asset.SerialNumber. Ooops - doesn't work because this OOTB field is not encryptable!