Create sequence in h2 database

Question

I want to create a sequence in h2 database for the below entity

public class Label {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "label_sequence")
  @SequenceGenerator(name = "label_sequence", sequenceName = "label_sequence", allocationSize = 100)
  private Long id;

  private String name;

  private String value;
}

Below is the sql command I am executing

CREATE SEQUENCE label_sequence
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1;

I am getting the following error:

Syntax error in SQL statement "CREATE SEQUENCE LABEL_SEQUENCE
  START WITH 1
  INCREMENT BY 1
  MINVALUE[*] 1 "; SQL statement:
CREATE SEQUENCE label_sequence
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1 [42000-140]

The below query works

CREATE SEQUENCE label_sequence
  START WITH 1
  INCREMENT BY 1;

But I am getting ID values less than 1 because of the allocation size I guess.

How can I make sure the ID values never go below 1?

score 2 · Answer 1 · answered Jul 18 '22 at 08:44

2

This should work:

CREATE SEQUENCE "MY_OWN_SEQ" 
MINVALUE 1 
MAXVALUE 999999999 
INCREMENT BY 1 
START WITH 202700 
NOCACHE 
NOCYCLE;

answered Jul 18 '22 at 08:44

ACV

9,964
5
76
81

score 0 · Accepted Answer · answered May 08 '20 at 06:08

0

You're using a very old version of H2 database, H2 1.2.140 was released 10 years ago. You need to use some modern version that knows this syntax.

If you really need to use such archaic version, just remove MINVALUE clause, your sequence will generate values 1, 2, … 9223372036854775807, so you have enough time before the overflow. You can add a CHECK constraint to your column for more safety, if you wish.

answered May 08 '20 at 06:08

Evgenij Ryazanov

6,960
2
10
18

When using the latest version, the minvalue problem is solved. But I am still getting negative ID values. – Phanindra May 08 '20 at 17:37
It means that your applications passes negative values to H2 and sequence isn't actually used. – Evgenij Ryazanov May 09 '20 at 02:23

score 0 · Answer 3 · answered Aug 31 '23 at 08:26

0

You must also add Mode=Oracle to the properties file to be Oracle compatible with sequence request syntax.

See https://stackoverflow.com/a/64799048

answered Aug 31 '23 at 08:26

Etienne Griette

1

The question is about sequence creation, not about Oracle compatibility mode. – Jochen Reinhardt Sep 01 '23 at 14:54

Create sequence in h2 database

3 Answers3