19

I am trying to create a table in an H2 database. How do I specify that the primary key should be generated from a sequence that has been created?

The sequence is called group_seq, and I created it using this statement:

CREATE SEQUENCE GROUP_SEQ;

So when I create the table, how do I specify that I want my primary key col (ID) to use that sequence?

M-Razavi
  • 3,327
  • 2
  • 34
  • 46
user1154644
  • 4,491
  • 16
  • 59
  • 102

1 Answers1

38

If you want to use your own sequence:

create sequence group_seq;
create table test3(id bigint default group_seq.nextval primary key);

And if not:

create table test1(id identity);

or

create table test2(id bigint auto_increment primary key);

All this is documented in the H2 SQL grammar railroad diagrams.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • how do I specify that id is the primary key? – user1154644 Sep 30 '13 at 17:22
  • @ThomasMueller When I use my custom sequence it doesn't get auto_generated by Hibernate. When I use the auto_increment flag, it works fine. I check in Information_Schema.Sequences and found that the column IS_GENERATED is set to true for system generated sequences but false for custom sequences. Is there a way I can set it to true for custom sequences? – Arham Oct 08 '17 at 10:45
  • https://stackoverflow.com/questions/61321952/h2-db-id-nextval `id BIGINT DEFAULT NEXT VALUE FOR group_seq not null primary key` – S34N Oct 25 '22 at 05:38