Tip #894: Alternate key, duplicate data, and solutions

tl;dr

If you have duplicate values present in a field, creating alternate key on that field will fail. However, when a solution that includes alternate key is imported into the deployment where duplicates are present, the import does not fail and completes with the warning indicating that alternate key was not created.

Long story

Not every day you hear that kind of statement from Neil “New CRM Release – New Country” Benson

Well that’s embarrassing…

Long story short, Neil was trying to create an alternate key but kept receiving the message:

The error message on the system job to create the alternate key and index is:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.foobar_departmentBase' and the index name 'ndx_for_entitykey_foobar_deptkey'. The duplicate key value is (BARBAZ).

If you get this error, it means that you have more than one record with the value BARBAZ in the field selected for the alternate key.

Sounds simple enough, right? Until Jonas “Surströmming MkII” Rapp chimed in:

On the same topic – I created a similar key to prevent duplicates in my dev env. Exported solution and imported to test. There were duplicates in test, so I expected solution import failure. But the solution imported fine, I just got a warning that the index could not be created.

So failing import of some solution components/properties generate “import failed” while others only generate “import succeeded with warning”. There might be a reason for this that I am currently unaware of, but the inconsistent behavior worries me. At least until I know the good reason.

After some light debate, David “British Scientist” Jennaway gave us all if not a good reason then at least a very solid explanation:

I can see why some things result in warnings, and some in errors. My take on this is that anything that fails solely due to data in the destination (e.g. this example, and an invalid reference to a record in a workflow) is more likely to be a warning.

Share on FacebookTweet about this on TwitterShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *