9

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Candy Chiu
  • 6,579
  • 9
  • 48
  • 69

1 Answers1

15

In version 2.0, 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'

Update for HSQLDB 2.1 and later: A feature has been added to support this.

CREATE SEQUENCE SEQU
CREATE TABLE company ( id bigint GENERATED BY DEFAULT AS SEQUENCE SEQU PRIMARY KEY, name varchar(128) NOT NULL CHECK (name <> '') );

See the Guide under CREATE TABLE http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_table_creation

In addition, 2.1 and later has a PostgreSQL compatibility mode in which it accepts the PostgreSQL CREATE TABLE statement that references the sequence in the DEFAULT clause and translates it to HSQLDB syntax.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
fredt
  • 24,044
  • 3
  • 40
  • 61
  • Note that the default type for sequences is INTEGER. If you want to use BIGINT use: CREATE SEQUENCE AS BIGINT ... See http://www.hsqldb.org/doc/1.8/guide/ch09.html#create_sequence-section – anre Jul 06 '18 at 16:51