Article Original Creation Date: 2013-07-22

Overview

While trying to send the 'Add Device' with SOAP UI, returns the following error:

Unique Constraint (***.SPRT_EC_PK) Violated

Environment

SG4.2.2

Solaris 10

Oracle 10

JBoss4.0.4.GA

Root Cause

That unique constraint violation is on SPRT_EC_DEVICE.ID, which is the primary key for that table.

Service Gateway does not specify a value for the primary key when a new record is inserted into the SPRT_EC_DEVICE table: it gets auto-generated using the SPRT_EC_DEVICE_SEQ sequence.

The SPRT_EC_DEVICE_SEQ might have gotten reset, so it is now generating values that already exist in the database.

Resolution

1. Recreate the sequence so that it is generating values that do not already exist in the database.
2. You will have to do the following to drop and then recreate the sequence.
3. Use a value slightly larger than the value returned by:

SELECT MAX(ID) FROM SPRT_EC_DEVICE): DROP SEQUENCE SPRT_EC_DEVICE_SEQ;

4. Drop and recreate the sequence:

CREATE SEQUENCE SPRT_EC_DEVICE_SEQ START WITH INCREMENT BY 1 NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE NOORDER;