Unique constraint violation on primary key column with sequence provided id

Question

We are using Hibernate to insert rows into an Oracle table, with a sequence providing the ID's for the primary key column. When doing an insert, we get:

ORA-00001: unique constraint (TABLE_A.PK_ID) violated

How is this possible, if we rely on a sequence for unique ids?

Answer 1

It turns out that somehow the sequence became out of sync. The MY_SEQUENCE.nextVal must be higher than the highest ID in the table.

That means, select MY_SEQUENCE.nextVal from DUAL; must be higher than select max(ta.ID) from TABLE_A ta;

You need to update the sequence:

ALTER SEQUENCE MY_SEQUENCE INCREMENT BY 10000;

I didn't have the permission to run the above statement, so I just held down the execute button on select MY_SEQUENCE.nextVal from DUAL; for a while. It is still quicker than getting a DBA involved.