将序列链接到 hsqldb 中的标识

发布于 2024-09-04 04:57:29 字数 391 浏览 1 评论 0原文

在PostgreSql中，可以定义一个序列并将其用作表的主键。在 HsqlDB 中，我们仍然可以创建一个不链接到任何用户定义序列的自动增量标识列。是否可以使用用户定义的序列作为HsqlDB中自增标识列的生成器？

PostgreSql 中的示例 sql：

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 中的等效项是什么？

谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自找没趣 2024-09-11 04:57:29

在 2.0 版本中，没有直接的功能。您可以在表上定义 BEFORE INSERT 触发器来执行此操作：

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 for id 的情况下进行插入

INSERT INTO company VALUES null, 'test'

HSQLDB 2.1 及更高版本的更新： 添加了一项功能支持这一点。

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

此外，2.1 及更高版本具有 PostgreSQL 兼容模式，在该模式中，它接受引用 DEFAULT 子句中的序列的 PostgreSQL CREATE TABLE 语句，并将其转换为 HSQLDB 语法。

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.

回复收藏 0 原文

~没有更多了~