db2基础：约束

简介

本部分描述 DB2 与 solidDB SQL 过程的结构差别。

DB2 for Linux, UNIX, and Windows (DB2 LUW) 使用约束对数据执行业务规则。本文介绍以下类型的约束：

• NOT NULL 约束
• 唯一性约束
• 主键约束
• 外键约束
• 表检查约束

还有称为信息约束 的其他类型约束。不同于上面所列的五种约束， 数据库管理员不强制信息约束，但 SQL 编译器可用它来提高查询性能。 本文只关注清单中所列出的约束。

在创建新表时可定义一个或多个约束， 还可在修改表时定义约束。语句 CREATE TABLE 非常复杂。 事实上，在定义约束时只用到该语句的一小部分选项，如图 1 所示，这些选项如果放在语法图表中会显得非常复杂。

图 1. CREATE TABLE 语句的部分语法，用于定义约束
 

如果采用 DB2 Control Center ，约束管理会变得简单而方便。

约束定义与其所要应用的数据库相关联，并存储在数据库目录中，如 表 1 所示。可通过查询数据库目录来检索和检查相关信息。可直接通过命令行执行该操作（首先要确认数据库已连接），或者，还可利用 DB2 Control Center 来很方便地执行该操作。

可以像处理其他数据库对象一样，来处理约束。 要为其命名，要有相关的架构（创建者 ID），有些情况下还可丢弃（删除）。

图 2. CREATE TABLE 语句的部分语法，用于定义约束（续）
 

表 1 展示数据库目录中的约束信息。为了成功运行，可再次查询该目录来发起数据库连接请求。

表 1. 数据库目录中的约束信息

目录视图 视图列 描述 查询举例

SYSCAT.CHECKS		为每个表检查约束保持一个行	db2 select constname, tabname, text from syscat.checks
SYSCAT.COLCHECKS		为表检查约束所引用的每个列保持一个行	db2 select constname, tabname, colname, usage from syscat.colchecks
SYSCAT.COLUMNS	NULLS	表明一个列是（Y）否（N）可空	db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'
SYSCAT.CONSTDEP		为其他对象上约束的每个依赖项保持一个行	db2 select constname, tabname, btype, bname from syscat.constdep
SYSCAT.INDEXES		为每个索引保持一个行。	db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT'
SYSCAT.KEYCOLUSE		为每个唯一性、主键或者外键约束所定义的键所包含的列保持一个行	db2 select constname, tabname, colname, colseq from syscat.keycoluse
SYSCAT.REFERENCES		为每个参照约束保持一个行	db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references
SYSCAT.TABCONST		为每个唯一性（U）、主键（P）、外键（F）或者表检查（K）约束保持一个行	db2 select constname, tabname, type from syscat.tabconst
SYSCAT.TABLES	PARENTS	此表的父表数量（该表所依赖参照约束的数量）	db2 "select tabname, parents from syscat.tables where parents > 0"
SYSCAT.TABLES	CHILDREN	此表的相关表数量（此表作为父表的参照约束的数量）	db2 "select tabname, children from syscat.tables where children > 0"
SYSCAT.TABLES	SELFREFS	该表自引用参照约束的数量 （此表既作为父表又作为相关表的参照性索引的数量）	db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT.TABLES	KEYUNIQUE	该表所定义的唯一性（与主键不同）约束的数量	db2 "select tabname, keyunique from syscat.tables where keyunique > 0"
SYSCAT.TABLES	CHECKCOUNT	该表所定义的检查约束的数量	db2 "select tabname, checkcount from syscat.tables where checkcount > 0"

回页首

“不能为空！” —— NOT NULL 约束

NOT NULL 约束 避免在列中加入空值。 这确保了表中每列中都是有意义的值。 例如，在数据库 SAMPLE 中定义表 EMPLOYEE 时包含 LASTNAME VARCHAR(15) NOT NULL，来确保每一行都包含员工的姓。

要确定一列是否可空，可参考该表的数据定义语言（DDL，可通过调用 db2look 工具来生成）。 可利用 DB2 Control Center，如图 3 和图 4 所示。

图 3. Control Center 中的表视图 
 

利用 DB2 Control Center 可以很方便地访问表之类的数据库对象。 图 3 在数据库 SAMPLE 中展示用户表。 在对象树中选中表后，该表会出现在内容窗格中。 如果选择表 STAFF ，就可打开 Alter Table 窗口来查看表的定义， 包括图 4 中展示的列属性。

图 4. Control Center 当中的 Alter Table 
 

还可查询数据库目录，如清单 1 所示。

清单 1. 查询数据库目录来确定哪些列可以为空

db2 select tabname, colname, nulls
    from syscat.columns
    where tabschema = 'DELSVT' and nulls = 'N'

 

回页首

“必须唯一” —— 唯一性约束

唯一性约束 避免在表的特定的列内多次出现同一个值。还可避免在一组列内多次出现一组值。 唯一性约束所引用的列必须定义为 NOT NULL。可利用 CREATE TABLE 的 UNIQUE 子句来定义唯一性约束 （ 图 1 和 图 2），还可利用 ALTER TABLE 语句进行定义，如清单 2 所示。

清单 2 展示如何创建唯一性约束。除了表 ORG_TEMP 中的列 LOCATION 不能为空之外，表 ORG_TEMP 等同于数据库 SAMPLE 中的表 ORG，还可在列 LOCATION 上定义唯一性约束。

清单 2. 创建唯一性约束

db2 create table org_temp (
    deptnumb smallint not null,
    deptname varchar(14),
    manager smallint,
    division varchar(10),
    location varchar(13) not null)

db2 alter table org_temp add unique (location)

db2 insert into org_temp
    values (10, 'Head Office', 160, 'Corporate', 'New York')

DB20000I  The SQL command completed successfully.

db2 insert into org_temp
    values (15, 'New England', 50, 'Eastern', 'New York')

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DELSVT.ORG_TEMP" from having duplicate values for the index key.
SQLSTATE=23505

 

约束名

如果在创建约束时不为其命名，DB2 会基于创建时间戳来为其命名，比如 SQL100419222516560。

唯一性约束能避免错误复制，来确保数据完整性。 在本例中，唯一性约束避免了第二次插入用于将 New York 指定为组织分支机构的记录。 通过唯一性索引来实施唯一性约束。

回页首

"我们是第一！ " - 主键约束

主键约束 确保表中一列或一组列中构成主键的所有值唯一。 主键用于在表中标识特定的行。 一个表不能有多个主键，但可以有多个唯一键。 主键约束是唯一性约束的特例。 通过主键索引来实施主键约束。

主键约束所引用的列必须定义为 NOT NULL。 可利用语句 CREATE TABLE 的子句 PRIMARY KEY 来定义主键约束（见 图 1 和 图 2），还可如图 3 所示，利用 ALTER TABLE 语句来定义。

图 3 展示如何创建主键约束。表 STAFF 中的列 ID 不能为空，可为其定义主键约束。

清单 3. 创建主键约束

db2 alter table staff add primary key (id)

 

另外，可以利用 DB2 Control Center 在表上定义主键约束，如图 5 和图 6 所示。 Alter Table 窗口提供了在表上定义主键约束的便利方式。 选择 Keys 标签，然后单击 Add Primary。

图 5. Alter Table 窗口 
 

如图 6 所示，出现 Define Primary Key 窗口。

图 6. Define Primary Key 窗口 
 

Define Primary Key 窗口使得您可以从可用列列表中选择一个或多个列。 单击 > 按钮将名字从可用列列表中移动到选定列。 要注意选定列不能为空。

回页首

“都是相对的” —— 外键约束

外键约束 有时也称为参照约束。参照完整性 定义为，数据库中所有外键值都有效 。那么什么是外键？外键 是表中的一列或几列，这些列的值必须至少与父表中的一个主键值或唯一键值匹配。 这意味着什么？这意味着，如果表（T2）中一列（C2）的值匹配另一表（T1）中一列（C1）的值， 并且 C1 是 T1 主键所在的列，那么 C2 就是 T2 外键所在的列。 包含父键（是主键或唯一键）的表称为父表， 包含外键的表称为相关表。考虑如下例子。

数据库 SAMPLE 中的表 PROJECT 含有列 RESPEMP。 该列中的值表示负责表中所列出的每个项目的员工的员工工号。RESPEMP 不能为空。 因为该列与表 EMPLOYEE 中的列 EMPNO 相对应， 而 EMPNO 是表 EMPLOYEE 的主键， 如清单 4 所示，RESPEMP 可定义为表 PROJECT 的外键。 这确保今后对表 EMPLOYEE 进行删除操作时，不会导致表 PROJECT 出现不存在的 负责员工的情况。

可利用语句 CREATE TABLE 的子句 FOREIGN KEY 来定义外键约束 （见 图 1 和 图 2），或者如清单 4 所示，利用语句 ALTER TABLE 进行定义。

清单 4. 创建外键约束

db2 alter table project add foreign key (respemp) references employee on delete cascade

 

子句 REFERENCES 为参照约束指明父表。 定义外键约束的语法包括一个 规则子句（rule-clause），用于告诉 DB2 您想如何从参照完整性的角度来进行更新或删除操作（见 图 1）。

插入操作以标准方式进行，不需要您作控制。 参照约束的插入规则 要求外键的插入值必须匹配父表中父键的一些值。 这与前面的描述一致。 如果向表 PROJECT 中插入新记录，则该记录必须包含一个对表 EMPLOYEE 中已有记录的引用（通过父-外键关系）。

参照约束的更新规则 是要求有关外键 的更新值必须与父表中一些父键的值匹配， 并且当对父键的 更新操作完成时，所有外键值必须匹配父键值。 所有这些都意味着不能存在孤儿 ，并且每个相关表必须有父表。

当从父表中删除行时，依据在定义参照约束时所指定的选项，来应用参照约束的删除规则。

表 2. 参照约束选项

如果该子句是在创建参照约束时指定的... 那么结果会是

RESTRICT or NO ACTION	不删除任何行
SET NULL	外键的每个可空列设为空
CASCADE	删除操作传播到父表的相关表。 这些相关表与父表之间是 删除相关的（delete-connected） 。

清单 5 列出了几点。

清单 5. 演示外键约束的更新规则和删除规则

db2 update employee set empno = '350' where empno = '000200'
DB20000I  The SQL command completed successfully.

db2 update employee set empno = '360' where empno = '000220'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0531N  The parent key in a parent row of relationship
"DELSVT.PROJECT.FK_PROJECT_2" cannot be updated.  SQLSTATE=23504

db2 "select respemp from project where respemp < '000050' order by respemp"

RESPEMP
-------
000010
000010
000020
000030
000030

  5 record(s) selected.

db2 delete from employee where empno = '000010'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0532N  A parent row cannot be deleted because the relationship
"DELSVT.PROJECT.FK_PROJECT_2" restricts the deletion.  SQLSTATE=23001

db2 "select empno from employee where empno < '000050' order by empno"

EMPNO
------
000010
000020
000030

  3 record(s) selected.

 

可以改变父表（EMPLOYEE）中 EMPNO 的值 000200 ， 因为在相关表（PROJECT）中没有 RESPEMP 的值 000200。 然而，对于 EMPNO 的值 000220，由于它匹配表 PROJECT 的外键值，因此无法修改它。 删除规则中指定选项 RESTRICT，来确保当删除相关的表 PROJECT 包含匹配的外键值时，不会删除表 EMPLOYEE 中包含主键值 000010 的行。

回页首

“反复检查” —— 表检查约束

表检查约束 对加入表的数据实施预先定义的约束。 例如，表检查约束能确保不论是在表 EMPLOYEE 中增加或者更新电话分机时，员工的电话分机长度都为 4 位数字。 可利用语句 CREATE TABLE 的子句 CHECK 来定义表检查约束（见 图 1 和 图 2），或者如清单 6 所示，利用语句 ALTER TABLE 来定义。

清单 6. 创建表检查约束

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

 

约束 PHONENO_LENGTH 确保增加到表 EMPLOYEE 中的电话分机正好为 4 位数字。

另外，如图 7 所示，可利用 DB2 Control Center 来定义表检查约束。

图 7. Alter Table 窗口提供了在列上定义表检查约束的便利方法 
 

单击 Add 按钮来定义新的约束， 打开 Add Check Constraint 窗口。或者单击 Change 按钮来修改已从列表中选择的现有约束，如图 8 所示。

图 8. 利用 Change Check Constraint 窗口可修改已有的检查条件 
 

如图 9 所示，如果表中现有行包含的值与新的约束冲突，则不能创建新的表检查约束。 正确更新不兼容的值以后，可以成功添加或者修改约束。

图 9. 如果新的表检查约束与表中现有的值不兼容，则返回一个错误 
 

推迟数据检查

利用语句 SET INTEGRITY 可将表设置为检查挂起状态。 这允许在对表中的数据不做任何检查的情况下，通过执行 ALTER TABLE 语句定义新的检查约束。

利用语句 SET INTEGRITY 可打开或关闭表检查约束。 这很有用，例如，可用于优化向表中加载大量数据的操作。 清单 7 展示如何使用语句 INTEGRITY 来编写简单方案的可能方法。 在本例中，员工的分机号 000100 更新为 123， 然后关闭表 EMPLOYEE 的完整性检查。 检查约束需要在表 EMPLOYEE 中定义 4 位的电话分机值。 创建了异常表 EMPL_EXCEPT。 定义的新表镜像了表 EMPLOYEE。 开启完整性检查，检查约束中冲突的行写入异常表。 再次查询这些表来确认异常表中已存在有问题的行。

清单 7. 利用语句 SET INTEGRITY 来推迟约束检查 

db2 update employee set phoneno = '123' where empno = '000100'

db2 set integrity for employee off

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

db2 create table empl_except like employee

db2 set integrity for employee immediate checked for exception in employee use empl_except
SQL3602W  Check data processing found constraint violations and moved them to
exception tables.  SQLSTATE=01603

db2 select empno, lastname, workdept, phoneno from empl_except

EMPNO  LASTNAME        WORKDEPT PHONENO
------ --------------- -------- -------
000100 SPENSER         E21      123

  1 record(s) selected.

 

回页首

结束语

本文探索了 DB2 for Linux, UNIX, and Windows 所支持的各类约束， 包括 NOT NULL 约束、唯一性 约束、主键约束、外键（参照）约束以及表检查约束。DB2 利用约束来对数据实施业务规则，并保证数据库的完整性。 您还可学习如何使用命令行和 DB2 Control Center（以及如何查询数据库目录）来高效地管理约束。