Duplicate Key Error During Upgrade of Enterprise Analyzer Workspace
Client receives error during attempted upgrade of workspace in Enterprise Analyzer. Upgrade log returns following message: [Oracle][ODBC][Ora]ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
. While upgrading UpgradeModel> <code: Fatal>
This error results from duplicate keys stored in a specific EA table. The table is usually identified in the few lines in the log leading up to the error message. A SQL query can be used to identify and remove the duplicates to allow the upgrade to proceed.
The query below can be modified for any table. In the example below, the table is RelProgramusesPsbModule.
delete from RelProgramUsesPsbModule
where ROWID in (select RID
from (select ROWID as RID,
ROW_NUMBER() OVER(PARTITION BY leftobject, rightobject order by leftobject, rightobject) as RNK
from RelProgramUsesPsbModule) TMP1
where TMP1.RNK > 1)