Why am I getting error "ORA-00955: name is already used by an existing object"?

If you perform an upgrade installation on a clinical data model after modifying a table's primary key (PK) or unique index, you may get the above error from Oracle Warehouse Builder (OWB).

This is because when you create a primary key on a table, the system generates a unique index on the PK columns and maintains a connection between the PK and the uinque index. If you drop the primary key, the system drops the unique index. However, if you have manually created a unique index on the same columns, the system does not drop the index and the PK and index are no longer connected.

If you subsequently modify a PK column or manually create a unique index, the system generates and runs reorg.sql with the following steps:

1. Drops the PK.

2. Alters the column (or whatever change you have made).

3. Recreates the unique index on the PK columns.

4. Recreates the PK.

This works, but the connection between the primary key and unique index is lost. If you make another manual change, reorg.sql fails with the above error message because it does not drop the unique index in Step 1 due to the disconnect, but does try to create the unique index in Step 3, causing the error.

If you do a Full installation, the system recreates the primary key, the unique index, and a connection between them. However, it also deletes all data in the model's Development lifecycle stage.