H2 Db, ID NextVal

I have a sequence as:

CREATE SEQUENCE OWNER_SEQ START WITH 1;

I need to trigger this sequence for OWNER table’s ID column

CREATE TABLE METADATA.OWNER
(
    OWNER_ID           NUMBER  NEXTVAL(METADATA.OWNER_SEQ),
    OWNER_FIRSTNAME    VARCHAR2(100),
    OWNER_LASTNAME     VARCHAR2(100),
    CONSTRAINT OWNER_PK PRIMARY KEY (OWNER_ID)
);

I tried several versions of NEXTVAL but all gives error

Answer

NUMBER data type is an Oracle compatibility thing, it shouldn’t be normally used in H2 at all, especially for primary keys, this data type is slow. It’s much better to use BIGINT or INTEGER instead.

---

Normally you should not use sequences too if you don’t need to share the sequence between multiple tables. You can use an identity column:

OWNER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY,

You can specify sequence options for identity columns too:

OWNER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1),

START WITH 1 is not very useful, however, because this start value will be used by default.

---

But if you have a real reason to use the sequence, you need to specify it as a DEFAULT value:

OWNER_ID BIGINT DEFAULT NEXT VALUE FOR METADATA.OWNER_SEQ,