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.

Answer 1

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

    @GeneratedValue(strategy = 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.

Answer 2

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.