Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Link a sequence with to an identity in hsqldb

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.

like image 766
Candy Chiu Avatar asked Jun 01 '10 23:06

Candy Chiu


1 Answers

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.

like image 152
fredt Avatar answered Sep 24 '22 00:09

fredt