在 Firebird 数据库中创建自动增量字段的最简单方法 [英] Easiest way to create an auto increment field in Firebird database

问题描述

是否有使用 Firebird 创建自动增量字段的简单方法?我已经安装了 FlameRobin 管理工具，但是通过该工具创建自动增量字段的过程很复杂.我不能仅通过单击复选框或使用 Flamerobin 以外的其他工具来创建这样的身份字段吗?

解决方案

Firebird 3 及更高版本

在 Firebird 3 中很容易创建，因为它引入了标识列.实际上，它是为您生成序列 + 触发器(如 Firebird 2.5 所示)的语法糖.

例如

创建表 t1 (默认生成的 id 整数作为身份主键)

Firebird 3 仅支持默认生成"，这意味着用户可以指定自己的 id 值(这可能会导致重复值错误)；始终生成";已在 Firebird 4 中

打开主键列的列属性

默认列属性，选择新生成器和创建触发器:

由 FlameRobin 生成的生成器(序列)和触发代码.请注意，与我上面的示例相反，此触发器允许用户指定自己的 id 值，并带有一些避免将来重复的逻辑.执行这个(不要忘记提交):

Is there an easy way to create auto increment field using Firebird? I have installed the FlameRobin admin tool, but the process of creating an auto increment field through the tool is complex. Can I not create such an identity field just by clicking a checkbox or using some other tool other than Flamerobin?

解决方案

Firebird 3 and later

In Firebird 3 it is easy to create, as it introduced identity columns. In practice it is syntactic sugar for generating a sequence + trigger (as shown for Firebird 2.5) for you.

For example

create table t1 (
   id integer generated by default as identity primary key
)

Firebird 3 only supports "generated by default", which means users are able to specify their own id values (which might lead to duplicate value errors); "generated always" has been added in Firebird 4.

See also the Firebird 3 release notes, section "Identity Column Type".

Firebird 2.5 and earlier

Firebird 2.5 and earlier do not have auto-increment fields. You need to create them yourself with a sequence (aka generator) and a trigger.

Sequence is the SQL standard term and generator is the historical Firebird term; both terms are available in the Firebird DDL syntax.

To create a sequence:

CREATE SEQUENCE t1_id_sequence;

To create a trigger to always generate the id on a table T1 with primary key ID:

set term !! ;
CREATE TRIGGER T1_AUTOINCREMENT FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.ID = next value for t1_id_sequence;
END!!
set term ; !!

See also: How to create an autoincrement column?

Using FlameRobin

FlameRobin also provides tooling to create a sequence + trigger for you. If you have an existing table, you can follow these steps:

1. Open the table properties:

2. Open the column properties of the primary key column

3. Default column properties, select new generator and create trigger:

4. Generator (sequence) and trigger code generated by FlameRobin. Note that contrary to my example above this trigger allows a user to specify their own id value, with some logic to avoid future duplicates. Execute this (and don't forget to commit):

这篇关于在 Firebird 数据库中创建自动增量字段的最简单方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！