3690664 - Error "The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name" with deployment failure

A deployment running in migrate data mode fails with an error similar to:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.<table_name>' and the index name '<index_name>'. The duplicated key value is(<duplicate_keys>).

The failure occurs after introducing a new unique index in an *-items.xml definition, for example:

<itemtype code="***" ...>
    <deployment table="***" typecode="***" />
    <indexes>
        <index name="***" unique="true">
            ...
        </index>
    </indexes>
</itemtype>

deployment failed, duplicate key, CREATE UNIQUE INDEX statement terminated , KBA , CEC-SCC-CLA-DEP , Deployments , Problem