Hiberate generates @GeneratedValue that causes UniqueConstraint violation

Question

I have code that uses Hibernate to write to an Oracle table. It uses a SequenceGenerator to generate unique id's. Say I have id's 1 through 40 in the database. What happens is that if any users are deleted from the table, it leaves a gap (say, id=24) in the id's in the table. Then, when a new user is created, the new user's id is set by Hibernate to 24.

Now there is a problem because the immediate next new user gets an id=25, which causes a UniqueConstraint exception.

Is there something I'm doing wrong? How do I make Hibernate stop generating sequence values that already exist in the table?

@Entity
@Table(name="User")
public class User {
    @Id
    @SequenceGenerator(name="UserGen", sequenceName="UserSeq")
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="UserGen")      
    private Integer id; 

    @Column(length=64, unique=true)
    private String username;
...

Here is the sequence info in Oracle:

CREATED 31-OCT-16
LAST_DDL_TIME   31-OCT-16
SEQUENCE_OWNER  USERSERVICE
SEQUENCE_NAME   USERSEQ
MIN_VALUE   1
MAX_VALUE   9999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG  N
ORDER_FLAG  N
CACHE_SIZE  20
LAST_NUMBER 81
PARTITION_COUNT 
SESSION_FLAG    N
KEEP_VALUE  N

Your diagnostic is wrong. The sequence doesn't care about gaps in the IDs of the table. It's just a sequence, which increments each time a next value is requested. You simply initialized your sequence with a min value that will cause the generator to generate values that are already used as IDs in the table. — JB Nizet, Nov 30 '16 at 22:38
You are right. I did diagnose it wrong. The real problem is this: http://stackoverflow.com/questions/9861416/hibernate-generates-negative-id-values-when-using-a-sequence — leontp587, Nov 30 '16 at 23:50

coladict · Accepted Answer · 2016-12-01T13:53:20.827

1

You have to define your SequenceGenerator to have the same allocationSize as the INCREMENT_BY value of your sequence.

@SequenceGenerator(name="UserGen", sequenceName="UserSeq", allocationSize = 1)

I've faced this problem before (in PostgreSQL) and eventually I just changed it to @GeneratedValue( strategy = GenerationType.IDENTITY ) and removed the SequenceGenerators entirely, since they were already used as default value on insert when not specified. It's a slight performance boost when your sequence increment size is 1, because with the SequenceGenerator Hibernate calls the sequence manually, using one extra query that it can spare.

edited Dec 01 '16 at 13:53

answered Dec 01 '16 at 13:45

coladict

4,799
1
16
27

1

Agreed. with the allocationSize=1. That worked for me. Important to note, for Oracle. GenerationType.IDENTITY isn't supported: According to: http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing "Identity sequencing uses special IDENTITY columns in the database to allow the database to automatically assign an id to the object when its row is inserted. Identity columns are supported in many databases, such as MySQL, DB2, SQL Server, Sybase and Postgres. Oracle does not support IDENTITY columns but they can be simulated through using sequence objects and triggers." – leontp587 Dec 01 '16 at 16:42

Hiberate generates @GeneratedValue that causes UniqueConstraint violation

1 Answers1