DB2 表中的生成列和标识列

 

简介

生成列是 IBM® DB2® Universal Database™ 的一个便捷特性。 一个生成列（generated column）是从表达式而不是从 INSERT 或 UPDATE 操作派生其值。

回页首

生成列和标识列

请参考下列数据定义语言（DDL），它将在数据库中创建一个表：

db2 create table db2admin.actor ( actor_id int generated by default as identity , actor_name varchar(20) , act_yr_of_birth smallint , act_yr_of_death smallint , age_at_death smallint generated always as (act_yr_of_death - act_yr_of_birth) ) in userspace1db2 alter table db2admin.actor add primary key (actor_id)

该 DDL 包含生成列的简单示例。通过使用下列表达式，AGE_AT_DEATH 列由 DB2 自动生成：

ACT_YR_OF_DEATH - ACT_YR_OF_BIRTH = AGE_AT_DEATH

通过在 CREATE TABLE 或 altER TABLE 语句中为列指定 GENERATED ALWAYS AS ... 子句，可以在表中创建生成列。

我的示例还包括特定类型的生成列（称为标识列）的定义。 标识列包含可由 DB2 自动生成的数值。对于创建主键值而言，标识列确实有用。实际上，该示例包含一个 altER TABLE 语句，它将 ACTOR_ID 列定义成 ACTOR 表的主键。

始终由 DB2 生成：DB2 可以保证以 GENERATED ALWAYS 方式定义的标识列的唯一性，因为它的值始终是由 DB2 生成的。

我使用了 可以一词，因为许多用户误解了这种唯一性保证。这可能对也可能不对，它取决于您如何定义标识列的属性。例如，假定您已经定义了一个标识列，它在最小值 1 和最大值 10 之间循环：根据定义，除非该表包含的行非常少（10 行或更少），否则该列中的值将不唯一。

保证列值唯一性的方法就是对列定义唯一性约束。上述表中的 actor_id 列之所以能够保证唯一性，是因为我使用了 alter table 命令将该列定义为表上的主关键字。您是不是想知道为什么我选择使用标识列，这是因为我们可以在不产生与设置表计数器有关的并发性问题的情况下生成数值。

用户提供的标识：以 GENERATED BY DEFAULT 方式定义的标识列将接受由应用程序提供的值。如果应用程序不为带有这类列的表提供标识值，则 DB2 会为您生成那些值（但是它不能保证标识值的唯一性，除非已经为该表定义了唯一约束 — 就象我的示例那样）。

在我的 actor 表中，我以 GENERATED BY DEFAULT 的方式定义了 actor_id 列，这意味着，如果我愿意，那么我可以插入自己的 actor 标识。例如，下列 INSERT 语句将三个新的行添加到 ACTOR 表：

db2 insert into db2admin.actor (actor_id, actor_name, act_yr_of_birth) values (150,'Bruce Willis',1955), (default,'Tom Cruise',1962), (default,'Tommy Lee Jones',1946)

在上面的示例中，为第一行指定了标识值（在本例中是 150），DEFAULT 关键字用来说明 DB2 将为其余行生成标识值。在运行了这个 INSERT 语句后，您可以看到由 DB2 为 Tom Cruise 和 Tommy Lee Jones 生成的值，还可以看到用户为 Bruce Willis 提供的值 150。通过查看如 图 1所示的 ACTOR 表的内容，您可以验证这一点。

图 1. ACTOR 表的内容

如果我以 GENERATED ALWAYS 方式（不是 GENERATED BY DEFAULT 方式）定义 actor_id 列，那么要成功地完成运行，本示例中的 INSERT 语句将不得不指定 DEFAULT 关键字，而不是值 150；否则应用程序将收到 SQL00798 错误。

自己动手试着进行下面的工作。利用与创建 ACTOR 表相同的 DDL 创建名为 ACTOR2 的新表，只是对下面的 粗体部分进行一些更改：

db2 create table db2admin. actor2 ( actor_id int generated always as identity , actor_name varchar(20) , act_yr_of_birth smallint , act_yr_of_death smallint , age_at_death smallint generated always as (act_yr_of_death - act_yr_of_birth) ) in userspace1db2 alter table db2admin. actor2 add primary key (actor_id)

现在，对 ACTOR2 表运行与用于 ACTOR 表相同的 INSERT 语句（别忘了更改： ...insert into db2admin. actor2... ）。发生了什么？您收到 SQL00798N 错误。

现在，请运行相同的 INSERT 语句，只是对下面的 粗体部分进行一些更改：

db2 insert into db2admin. actor2 (actor_id, actor_name, act_yr_of_birth) values ( default,'Bruce Willis',1955), (default,'Tom Cruise',1962), (default,'Tommy Lee Jones',1946)

成功了！为什么呢？因为 ACTOR2 表创建时带有要求 DB2 必须生成标识值的选项。现在，如果浏览 ACTOR2 表的内容，它看上去将如 图 2所示。

图 2. ACTOR2 表的内容

现在您应该有两个表：

• ACTOR（利用 GENERATED BY DEFAULT 选项定义的）
• ACTOR2（利用 GENERATED ALWAYS 选项定义的）。