DB2 业务规则的应用实践（1）

业务规则

业务规则是定义业务的某些特征的一系列语句。在大多数企业中，数据必须遵循一组特定的业务规则和限制。 可以使用 约束 在数据库层有效地实现这些限制。

DB2 中可用于业务规则的 约束类型 如下：

• NOT NULL
• DEFAULT
• CHECK
• UNIQUE
• Primary Key（主键）
• Referential integrity（外键约束 或 参照完整性）

• NOT ENFORCED informational（信息约束）

  约束通常在表创建期间定义，但是也可以通过使用 ALTER TABLE 语句将它们添加到现有表。

NOT NULL

默认情况下，表中的每一列都将接受 NULL 值。

NOT NULL 约束防止在列中输入空值。

一旦在列或列集上定义了 NOT NULL 约束，试图向该列插入空值的任何 INSERT 或 UPDATE 操作都将失败。

NOT NULL约束与表中的单个列或一组特定列相关联，这些列通常是使用 CREATE TABLE 语句在表创建过程中定义的。也可以使用 ALTER TABLE 语句更改现有表的列约束，如下所示：

 -- Table Creation Process
 CREATE TABLE DEMO.EMPLOYEE (
     EMPID INTEGER NOT NULL,
     NAME VARCHAR (30),
     SSN VARCHAR (11) NOT NULL)
 IN USERSPACE1;

 -- Table Attribute Modification Process
 ALTER TABLE  DEMO.EMPLOYEE  ALTER COLUMN  SSN  SET NOT NULL ;
 
 

"ALTER TABLE... SET NOT NULL" 约束 在已经存在的列组织表上不受支持(SQL1667N)。在这种情况下， 唯一的选项是使用 NOT NULL约束创建新的表，并复制数据。注意，不能使用 ADMIN_MOVE_TABLE() 过程从一个列组织的表复制到另一个表。

DEFAULT 约束

DEFAULT 约束允许您指定在应用程序不提供值的情况下写入列的预定义值。

提供的预定义值可为 NULL（如果 未针对列定义NOT NULL约束）、用户或应用程序提供的具有兼容数据类型的值 或 DB2数据库管理器提供的值。

DEFAULT 约束仅应用于通过 INSERT, IMPORT, LOAD 或 INGEST命令插入到表中的新行。

对于现有记录，DB2数据库管理器将提供下表所示的默认值：

数据类型：SMALLINT, INTEGER, DECIMAL, NUMERIC, NUM, REAL, FLOAT, DOUBLE, DOUBLE RECISION

默认值：0

数据类型：CHARACTER, GRAPHIC

默认值：一串空白字符

数据类型：CHARACTER VARYING, CHAR VARYING, or VARCHAR, LONG VARCHAR, VARGRAPHIC, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB

默认值：一个零长度字符串

数据类型：DATE

默认值：在将行添加到表时从 CURRENT_DATE 特殊寄存器获得的系统日期（当日期列被添加到现有表时，现有行被分配给日期 January 01，0001）

数据类型：TIME

默认值：在将行添加到表中时从 CURRENT_TIME 特殊寄存器获得的系统时间。（当将时间列添加到现有表时，现有行被分配时间 00:00:00）

数据类型：TIMESTAMP

默认值：向表中添加行时从 CURRENT_TIMESTAMP 特殊寄存器(包括微秒)获得的系统日期和时间（当将时间戳列添加到现有表中时，将为现有行分配与January 01, 0001 - 00:00:00.000000 相对应的时间戳）

数据类型：XML

默认值：不适用。 唯一可能的默认值是NULL。

数据类型：任何不同的 user-defined 数据类型

默认值： 如果使用不同的类型定义列，则列的默认值是源数据类型转换为不同类型的默认值。

示例：

 -- Table Creation Process
 CREATE TABLE DEMO.EMPLOYEE (
     EMPID INTEGER NOT NULL, NAME VARCHAR (30),
     SSN VARCHAR (11) WITH DEFAULT '999-99-9999')
 IN USERSPACE1;

 -- Table Attribute Modification Process
 ALTER TABLE DEMO.EMPLOYEE ALTER COLUMN SSN  SET DEFAULT '999-99-9999';
 

ALTER TABLE... SET DEFAULT 默认约束在现有列组织表中不支持(SQL1667N)。在这些情况下，唯一的选项是创建具有默认约束的新表并复制数据。

CHECK 约束

CHECK 约束是这样一种数据库规则，它指定表中每行的一列或多列中允许使用的一系列值。

可以使用多个比较运算符构建简单的 CHECK 约束，如 >、<、>=、<=、=和<>。还可以使用 AND、OR、NOT（布尔运算符）和 LIKE 构建复杂的 CHECK 约束。（使用通配符如 % 和 _）或 IN 谓词。

对 CHECK 约束进行评估，以返回 TRUE 或 FALSE 的值。通过 CHECK 约束的值被插入或更新到表中。 未通过 CHECK 约束的值将被拒绝，SQL错误SQL0545N将返回给应用程序。

CHECK 约束与表中的单个列或一组特定列相关联，这些列通常在使用 CREATE TABLE 语句的表创建过程中定义。它们还可以使用 ALTER TABLE 语句添加到现有表中：

 -- Table Creation Process
 CREATE TABLE demo.employee (
     empid INTEGER,
     name VARCHAR(30),
     ssn VARCHAR(11) NOT NULL,
     salary INTEGER CHECK (salary >= 5000),
     job VARCHAR(10) CHECK (JOB IN ('Engineer','Sales','Manager')));

 -- Table Attribute Modification Process
 ALTER TABLE demo.employee 
     ADD CONSTRAINT check_salary CHECK (salary >=5000)
     ADD CONSTRAINT check_job CHECK (JOB in ('Engineer','Sales','Manager'));

在列组织（BLU加速数据库）表中不支持 CHECK约束，如果尝试创建 CHECK约束，则会收到SQL错误 SQL1666N。

UNIQUE 约束

唯一约束确保分配给表中的一个或多个列的值始终是唯一的，且不为 NULL。

与 NOT NULL、DEFAULT 和 CHECK 约束不同，这些约束只能与表中的单个列相关联，唯一约束可以与单个列或一组列关联。 在唯一约束中指定的列必须定 义为 NOT NULL。

Unique Index

创建唯一约束时， DB2数据库管理器将检查此约束，以确定唯一约束所引用的列的索引是否已经存在。如果存在这样的索引，该索引将被标记为唯一的。如果没有，则使用 SYSIBM 模式创建适当的索引，并将其标记为唯一的 。

在 INSERT 或 UPDATE 操作期间违反唯一约束的任何记录都将被拒绝，SQL错误SQL0803N将返回给应用程序。

可以使用 CREATE TABLE 语句在表创建过程中定义唯一约束，也可以使用 ALTER TABLE 语句将其添加到现有表中，如下所示：

 -- Table Creation Process
 CREATE TABLE demo.employee (
     empid INTEGER NOT NULL UNIQUE,
     name VARCHAR(30),
     ssn VARCHAR(11));

 -- Table Attribute Modification Process
 ALTER TABLE demo.employee
     ADD CONSTRAINT u1_employee      UNIQUE (empid);

说明：

1. 行组织表 和 列组织表 都支持唯一约束。
2. 具有 XML 数据类型、结构类型 或 LOB数据类型的列不能参与唯一约束。
3. 在数据库分区特性(DPF)中，也称为大规模并行处理(MPP)环境中， 唯一约束参与列键必须是在 CREATE TABLE 语句的 DISTRIBUTE BY子句中指定的分发键的超集。

主键约束

主键是与唯一约束具有相同属性的 一个列或列的组合。因为主键用来标识表中的一行，所以它必须是唯一的，并且必须具有 NOT NULL 属性。

一个表只能有一个主键，但可以有多个唯一键 。主键是可选的，可以在创建或更改表时定义。

当导出或重组数据时，主键可以对数据进行排序，所以它们也是有益的。

可以使用 主键约束 和 外键约束 来定义表之间的关系。

引用完整性约束

引用完整性约束（也称为 外键约束 或 引用约束）使您能够定义 不同表之间 和 表内 所需的关系。 外键是表中的一列或一组列，它的值需要与其父表中至少一个主键或唯一键值相匹配。

引用约束 是这样一种规则，仅当满足下列其中一个条件时，外键的值才有效：

• 它们作为父键的值出现。也就是，外键的非空值必须与父表的父键的某些值相匹配。

• 外键的某些组成部分为 NULL。

  可以在 CREATE TABLE 语句或 ALTER TABLE 语句中定义引用约束。引用约束由数据库管理器在执行 INSERT、UPDATE、DELETE、ALTER TABLE、MERGE、ADD CONSTRAINT 和 SET INTEGRITY 语句时强制实施。

说明：

1. 用于创建引用约束的外键的列的名称不必与用于创建约束的主键的列的名称相同。但是，构成引用约束的主键和外键的列的数据类型必须相同。

2. 列组织的表不支持引用完整性约束。

   外键约束中“父/子表”的 “insert/update/delete 规则”

   当用户试图以违反引用完整性的方式更改数据时，会发生什么情况？如果不提供引用完整性检查，那么让我们来看看一些可能危及数据完整性的场景：

• INSERT operation： 将一个数据行插入到“子表”中，该行的外键列中的值与相应父表的父键中的值不匹配。
• UPDATE operation (child)： 将“子表”的外键列中的值更改为一个在相应父表的父键中没有匹配值的值。
• UPDATE operation (parent)： 将父表的父键中的值更改为一个在子表的外键列中没有匹配值的值。

• DELETE operation： 从父表中移除在“子表”的外键列中具有匹配值的记录。

  数据库管理器通过强制执行与每个引用约束关联的一组规则来处理这类情况。这组规则包括：

• 插入规则
• 更新规则
• 删除规则

插入规则

引用约束的插入规则为：“子表”的外键的非空插入值必须与父表的父键的某些值相匹配。

INSERT 规则适用于“子表 ”，它保证了只有与父表的父键值 关联 匹配的值才会插入到“子表”的外键中。任何试图将记录插入到违反 INSERT规则的子表中的行为都将导致 SQL0530N 错误，插入操作将失败。

注意，此规则不适用于父表，当记录添加到父表的父键时，不会发生数据验证。

更新规则

更新规则控制如何对参与引用完整性约束的“父表” 和“子表”执行更新 操作。 在更新父表的某行或从属表（子表）的某行时，应用更新规则。

更 新规则有两种可能的行为，它们如下所示：

• ON UPDATE RESTRICT ： 当父键的某列中的值更新时，如果从属表中的任何从属行与该键的原始值相匹配，更新会被拒绝。
• ON UPDATE NO ACTION：(1) 当父键的某列中的值更新时，假设如果在更新语句完成时从属表中的任意行没有相应的父键值匹配（排除后触发器），那么会拒绝更新；(2) 如果更新语句完成时每个子代都有父键，那么父代唯一键可以更新。
• 如果是从属行，当指定外键时，NO ACTION 更新规则是隐式的。NO ACTION 意味着更新语句完成时，外键的非空更新值必须与父表的父键的某些值相匹配。 如果组合外键的值的任何组成部分为空，那么该值为空。

RESTRICT 与 NO ACTION 区别：

 更新规则 RESTRICT 将在所有其他约束之前（包括修改 CASCADE 或 SET NULL 之类的规则的引用约束）执行。更新规则 NO ACTION 将在其他引用约束之后强制执行。注意，返回的 SQLSTATE 根据更新规则是 RESTRICT 还是 NO ACTION 而有所不同。

删除规则

删除规则控制如何针对参与引用完整性约束的 父表 执行删除操作。有四种删除规则： NO ACTION、RESTRICT、CASCADE 或 SET NULL。仅当外键的某些列允许空值时，才能指定 SET NULL。

ON DELETE CASCADE：这确保每当删除引用完整性约束的父表中的记录时，子表中所有在外键中具有匹配主键值的从属记录也将被删除。

ON DELETE SET NULL：这确保了每当删除引用完整性约束的父表的记录时，子表中所有在外键中具有匹配主键值的从属记录都设置为 NULL。要使此定义正常工作，外键列应允许空值，否则当 DB2试图设置 NOT NULL列的值时，将收到 SQL0629N 错误。

ON DELETE RESTRICT：这确保每当对父表执行 DELETE 操作时，子表中每一行的外键值将在执行删除操作之前的父表的主键中具有完全相同的匹配值。

也就是说，如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录。

ON DELETE NO ACTION：这确保每当删除引用完整性约束的父表中的记录时，子表中每个记录的外键值将在对应父表的主键中具有匹配值。

与 ON DELETE RESTRICT子句不同，该值可能与执行删除操作之前的值不同。如果在构建引用完整性约束时没有指定删除规则子句，则默认情况下使用 ON DELETE NO ACTION子句。

规则的执行顺序：

1. RESTRICT
2. SET NULL OR CASCADE
3. NO ACTION