CREATE UNIQUE INDEX statement terminated because a duplicate key was found

Products

• CA Service Catalog
• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

When attempting to upgrade Service Management, the upgrade fails and the INSTALL_MDB.LOG file contains errors similar to:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.crwf' and the index name 'crwf_x0'. The duplicate key value is (cr:3068186, 55)

Environment

Release:  14.x and above
CA Service Management

Cause

This is caused by some bad data in the MDB database. Basically, there is the same sequence task # for the same ticket #. It is almost like the ticket has multiple references to the same workflow. This needs to be cleaned up. 

What caused the original corruption is not known, it is likely due to external functionality allowing the creation of duplicate sequence #s (possibly through direct modifications to the MDB database).

Resolution

Based on the error above, run the following query against the MDB database:

select * from crwf where object_id='cr:3068186'

Then proceed to delete the duplicate row(s).

Additional Information

NOTE:  There might be more of these duplicate row errors.  If you then rerun the installer and it fails on another duplicate row, you would need to delete that duplicate row as well. In addition, you could try querying the MDB for all records which have the same CR number and more than one identical sequence number.