CODEWITHSUNDEEP
sqlhibernateh2
Phanindra
Phanindra

Reputation: 157

Create sequence in h2 database

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?

Upvotes: 5

Views: 26812

Answers (3)

Etienne Griette
Etienne Griette

Reputation: 1

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

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

Upvotes: 0

ACV
ACV

Reputation: 10561

This should work:

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

Upvotes: 3

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

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.

Upvotes: 0

Related Questions