Unique constraint violated - debug

Question

We have an application which is working for years and we are using same oracle database too. But migrated our database from one host to another host.

DB: ORACLE

Now all of the sudden we are getting following exception,

“org.springframework.dao.DataIntegrityViolationException: ORA-00001: unique constraint (YYY.XXX_LOG_PK) violated; 
SQL [n/a]; constraint [YYY.XXX_LOG_PK]; nested exception is org.hibernate.exception.ConstraintViolationException: ORA-00001: unique constraint (YYY.XXX_LOG_PK) violated”

code:

@SequenceGenerator(name = "TT_SEQUENCE_GENERATOR", sequenceName = "YYY.XXX_LOG_SEQ")
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TT_SEQUENCE_GENERATOR")
@Column(name = "ID")
public Long getId() {
    return this.id;
}

Sequence in DB:

CREATED 07-NOV-17
LAST_DDL_TIME   07-NOV-17
SEQUENCE_OWNER  TT
SEQUENCE_NAME   YYY.XXX_LOG_SEQ
MIN_VALUE   1
MAX_VALUE   999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG  N
ORDER_FLAG  N
CACHE_SIZE  0
LAST_NUMBER 75305

Problem: When we are trying to insert some record through JPA code we are getting the above exception but when I try to insert some record into the DB using sequence.nextval, it is not giving any exception.

Is there anyway I can debug to find out what would be the exception ? I also tried show_sql - I couldn't able to find the solution with this too, as this doesn't print the next sequence number in the console

Please point me in right direction, if you know the solution.

That the error doesn't occur with raw inserts on your Oracle database makes this problem even more insidious. Is there any chance that Hibernate is creating one or more junction tables behind the scenes, and that this is where the error is actually happening? — Tim Biegeleisen
I don't think there is a chance to call hibernate multiple times .... But the right point, I will check it by placing show_sql. — Yaswanth

Edwin Edwin · Accepted Answer · 2017-12-13T02:08:04

The most common scenario in which a self-augmented sequence encounters a unique constraint conflict is when the data is migrated, causing the maximun value of the data to exceed the sequence value.

Firstly query the sequanence current values:

SELECT seqname.CURRVAL FROM dual

And then modify the sequence value to make sure the sequence's nextval exceeds the current maximum value of the data.

ALTER SEQUENCE seqname INCREMENT BY XXXXXX;
SELECT seqname.NEXTVAL FROM dual;

Unique constraint violated - debug

2 Answers