将序列链接到hsqldb中的标识 [英] Link a sequence with to an identity in hsqldb
问题描述
在PostgreSql中,可以定义一个序列并将其用作表的主键.在HsqlDB中,仍然可以完成创建一个自动递增的标识列,该列不链接到任何用户定义的序列.是否可以使用用户定义的序列作为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?
PostgreSql中的示例SQL:
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 <> '')
);
HsqlDB中的等效项是什么?
What's the equivalent in HsqlDB?
谢谢.
推荐答案
在2.0版中,没有直接的功能.您可以在表上定义一个BEFORE INSERT触发器来执行此操作:
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;
并插入而不使用任何vlue作为ID
and insert without using any vlue for id
插入公司VALUES空值,测试"
INSERT INTO company VALUES null, 'test'
HSQLDB 2.1和更高版本的更新:已添加了一项功能来支持此功能.
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 <> '') );
请参见创建表"下的指南" http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_table_creation
See the Guide under CREATE TABLE http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_table_creation
此外,2.1和更高版本具有PostgreSQL兼容模式,在该模式下,它接受PostgreSQL CREATE TABLE语句,该语句引用DEFAULT子句中的序列并将其转换为HSQLDB语法.
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.
这篇关于将序列链接到hsqldb中的标识的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!