Identity Suite Request Submission Failure Due to Duplicate Key in dbo.Request Table

Products

• CA Identity Manager
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

Following an upgrade of Identity Suite (Identity Manager and Identity Portal), users may experience request submission failures. The system fails to process new requests and returns a duplicate key in dbo.Request table error. This article outlines the root cause and the procedure to reconcile the database sequence.

Environment

Identity Portal

Cause

During certain upgrade scenarios, the sequence object (dbo.SIGMA_REQUEST_MAIN_SEQ) responsible for generating primary keys in the dbo.Request table may be reset to 1. When the system attempts to create a new request using a sequence value that already exists in the table, a primary key constraint violation (duplicate key error) occurs.

Resolution

To resolve this issue, the sequence object must be updated to a value higher than the current maximum ID present in the dbo.Request table.

Warning: Please ensure you have a verified backup of your database before performing any manual modifications to database objects.

1. Identify the current maximum ID: Run the following query to find the highest primary key currently used in the Request table:

   SELECT MAX(id) FROM dbo.Request;

2. Update the Sequence: Using the value returned from the step above (for example, [MaxID]), update the sequence object to start from [MaxID] + 1:

   ALTER SEQUENCE dbo.SIGMA_REQUEST_MAIN_SEQ RESTART WITH [MaxID + 1];

   (Note: The exact syntax may vary slightly depending on your database platform, e.g., SQL Server vs. Oracle. Verify your database documentation for the specific ALTER SEQUENCE syntax if necessary.)

3. Verify: Attempt to submit a new request in the Identity Portal. The system should now increment the ID correctly without encountering duplicate key constraints.