[FIXED] Create sequence in h2 database

Issue

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?

Solution

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 By - Evgenij Ryazanov
Answer Checked By - Katrina (JavaFixing Volunteer)