auto incremented number generation for a non primary key column

Question

I have used the following id generation strategy for primary keys.

    @Id
        @GeneratedValue(strategy = IDENTITY)
        @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
        private Integer id;

I would like to do the same for a non primary key column. [a] What would be the semantics of defining an automatic generation scheme for such a key [b] Is there a guarantee that there will not be a gap in numbers for the generated number.

Possible duplicate of [hibernate not generate auto increment constraint on mysql table](https://stackoverflow.com/questions/9733619/hibernate-not-generate-auto-increment-constraint-on-mysql-table) — Vega, Oct 08 '18 at 14:51

score 7 · Answer 1 · edited Nov 28 '20 at 07:53

7

If you remove the @Id annotation, and keep the rest (changing the field name of course), that should work.

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "columnName", unique = true, nullable = false, insertable = false, updatable = false)
    private Integer columnName;

By allowing the database to generate the column values you will ensure that there are no gaps, except in the case of deletes and rollbacks.

For instance, if you delete rows in the middle of your table, then that will create a gap that will not be filled.

edited Nov 28 '20 at 07:53

Ali Behzadian Nejad

8,804
8
56
106

answered Mar 04 '10 at 13:28

James McMahon

48,506
64
207
283

1

Database don't provide this guarantee -- they only provide increasing unique number – ewernli Mar 04 '10 at 13:33
2

I am curious: Can i use @GeneratedValue(SEQUENCE) in some property other than a primary key, and its value will be generated ??? – Arthur Ronald Mar 04 '10 at 13:58
@Arthur Ronald F D Garcia, Yes, I believe so. I don't have a lot of experience generating the database from JPA entity classes, as I usually create the database and then generate my entity classes based on that. This should work though, let me know if you find differently. – James McMahon Mar 04 '10 at 14:00
I tried the above suggestion many times on H2 and it seems to fail with a Hibernate validation error in the ValidateEventListener method. The suggestion doesn't seem to work for me. – Sam Mar 06 '10 at 05:06
This solution does not work. One way to make this work is to create a trigger in the database that populates the column from a sequence. – Sasi Aug 09 '11 at 21:16

score 2 · Answer 2 · answered Mar 04 '10 at 13:33

Normally, auto incremented value are ensured to be always increasing, but there can be gaps.

Gaps can happen if two inserts happen concurrently, and one transaction is rolled back, for instance (If the database were to ensure there is no gap, all transactions would need to be serialized.)

EDIT

Example for oracle taken from this page:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

I'm not familiar with Oracle. What happens if you don't create a cache? — James McMahon, Mar 04 '10 at 13:45
This page discusses sequence with cache and no cache http://www.arikaplan.com/oracle/ari82599.html. Anyway, it was just to point out that some stuff are out of hibernate's hands and are at the database level and can then be database specific, unless you have full control over the way the number are generated and used at the applicative level. — ewernli, Mar 04 '10 at 14:41

score 1 · Answer 3 · answered Jun 26 '21 at 10:46

1

I solved it using columnDefinition property in @Column annotation.

@Column(name = "non_pk_column", columnDefinition = "BIGINT(20) NOT NULL UNIQUE KEY auto_increment")
Long nonPkColumn;

answered Jun 26 '21 at 10:46

Vasudev

803
1
7
16

auto incremented number generation for a non primary key column

3 Answers3