Link a sequence with to an identity in hsqldb

Question

In PostgreSql, one can define a sequence and use it as the primary key of a table. In HsqlDB, one can still accomplish creating an auto-increment identity column which doesn't link to any user defined sequence. Is it possible to use a user defined sequence as the generator of an auto-increment identity column in HsqlDB?

Sample sql in PostgreSql:

CREATE SEQUENCE seq_company_id START WITH 1;

CREATE TABLE company ( id bigint PRIMARY KEY DEFAULT nextval('seq_company_id'), name varchar(128) NOT NULL CHECK (name <> '') );

What's the equivalent in HsqlDB?

Thanks.

Answer 1

There is no direct feature for this. You can define a BEFORE INSERT trigger on the table to do this:

CREATE TABLE company ( id bigint PRIMARY KEY, name varchar(128) NOT NULL CHECK (name <> '') );

CREATE TRIGGER trigg BEFORE INSERT ON company REFERENCING NEW ROW AS newrow FOR EACH ROW SET newrow.id = NEXT VALUE FOR seq_company_id;

and insert without using any vlue for id

INSERT INTO company VALUES null, 'test'