oracle 数据完整性

一、数据完整性的类型 空规则 空规则 （null rule）是定义在某一列上的规则，其作用是允许或禁止将要被插入或更新的数据行此列的为空（null），即没有 唯一列 唯一规则 （unique value rule）是定义在某一列（或某一列集）上的规则，其作用是确保将要被插入

一、数据完整性的类型

空规则

空规则（null rule）是定义在某一列上的规则，其作用是允许或禁止将要被插入或更新的数据行此列的值为空值（null），即没有值

唯一列值

唯一值规则（unique value rule）是定义在某一列（或某一列集）上的规则，其作用是确保将要被插入或更新的数据行此列（或列集）的值是唯一的。

主键值
主键值规则（primary key value rule）是定义在某一键（key）（键指一列或一个列集）上的规则，其作用是确保表内的每一数据行都可以由某一个键值唯一地确定。

引用完整性规则

引用完整性规则（referential integrity rule）是定义在某一键（key）（键指一列或一个列集）上的规则，其作用是确保任意键值都能与相关表（related table）的某一键值（即引用值（referenced value））相匹配

在引用完整性中还包含了如下规则：对引用值可以进行哪些类型的数据操作（data manipulation），以及这些操作将如何影响依赖值（dependent value）。引用完整性中包含的具体规则有：

• 限制（Restrict）：不允许对引用值进行更新与删除。

• 置空（Set to Null）：当因引用值被更新或删除后，所有受影响的依赖值都将被置为NULL。

• 置默认值（Set to default）：当引用值被更新或删除后，所有受影响的依赖值都将被赋予一个默认值。

• 串联操作（Cascade）: 当引用值被更新后，所有受影响的依赖值也将被更新为相同的值。当引用数据行（referenced row）被删除后，所有受影响的依赖数据行（dependent row）也将被删除。

• 无操作（No Action）: 不允许对引用值进行更新与删除。此规则与RESTRICT 有所不同，她只在语句结束时进行检查，如约束被延迟（deferred）则在事物结束时进行检查。（Oracle 的默认操作为无操作。）

复杂完整性检查

复杂完整性检查（complex integrity checking）是一种用户定义的规则，针对某一列（或某一列集），其作用是依据数据行的列值来允许或禁止插入，更新，或删除此数据行。

二、完整性约束描述

完整性约束（integrity constraint）指以显式声明的方式为数据表的列定义规则。Oracle 支持以下类型的完整性约束：

• NOT NULL（非空）约束（constraint）是关于列中空值（null）的规则

• UNIQUE key（唯一键）约束是关于唯一列值（unique column value）的规则

• PRIMARY KEY（主键）约束是关于主标识值（primary identification value）的规则

• FOREIGN KEY（外键）约束是关于引用完整性（referential integrity）规则。用户可以在FOREIGN KEY 完整性约束中定义用完整性操作（referential integrity action），这些操作包括：
  

• 更新无操作（update No Action）及删除无操作（delete No Action）

• 删除串联操作（DeleteCASCADE）

• 删除置空（Delete SET NULL）

• CHECK（检查）约束，强制实现复杂完整性规则（complex integrity rule）

提示：

如果子表（child table）及父表（parent table）位于分布式数据库系统（distributed database）的不同节点（node）上，则无法通过声明的完整性约束（declarative integrity constraint）强制实现引用完整性（referential integrity）规则。此种情况下可以使用数据库触发器（详见下节）。

UNIQUE 键约束与 NOT NULL 约束结合使用

如 图 21-3 与 图 21-4 所示，定义了 UNIQUE 键约束的列可以输入空值（null），而同时定义了NOT NULL约束的列则不能输入空值。由于空值与任何值比较都无意义，因此定义了 UNIQUE 键约束而没有定义NOT NULL 约束的列上可以包含多行值为空的记录。多行列为空值（或复合UNIQUE 键（compositeUNIQUE key）的所有列均为空值）不会违反 UNIQUE 键约束

引用完整性约束

在关系型数据库中，不同的表可以依据其共同的列产生关联关系，数据库需要确保数据遵从列关系的规则。引用完整性规则（referential integrity rule）就是用于确保列关系的规则。
以下是与引用完整性约束（referential integrity constraint）相关的术语。

术语	定义
外键（foreign key）	引用完整性约束定义中包含的列（一列或多列），外键引用了引用键。
引用键（referenced key）	被外键引用表（可以是不同的表或同一个表）的唯一键（unique key）或主键（primary key）。
依赖表（dependent table）或子表（child table）	定义了外键约束的表。此表依赖于被引用的唯一键值或主键值。
引用表（referenced table）或父表（parent table）	被子表外键引用的表。此表的引用键决定了能否向子表插入数据或更新其中的数据。

外键约束可以定义于多列上。但复合外键（composite foreign key）必须引用列数及列数据类型均相同的复合主键（composite primary key）或复合唯一键（composite unique key）。由于复合主键及复合唯一键的列数不能超过 32 个，因此复合外键也受此限制。

空值与外键

关系型模型允许外键（foreign key）与其引用的主键（primary key）或唯一键（unique key）相匹配，或为空值（null）。如果一个复合外键的部分列为空，则此外键的非空列值无需与父键（parent key）中对应的列值相匹配。

在引用完整性中定义的操作

在引用完整性约束（referential integrity constraint）定义中，可以设定当被引用的父键（parent key）值被修改后，对子表（child table）中的依赖数据该执行何种操作。Oracle FOREIGN KEY 完整性约束定义中支持的引用操作（referential action）有UPDATE，DELETE NO ACTION，及DELETE CASCADE。

禁止操作

禁止操作（No Action）选项（默认操作）的含义是：如果对引用键值（referenced key value）的更新或删除操作将破坏引用完整性约束，则此操作不能执行。例如，当一个主键值被一个外键值引用时，这个主键值拥有依赖数据，所以不能被删除。

串联删除

串联删除（delete cascade）选项的含义是：如果包含引用键值（referenced key value）的数据行被删除，则子表（child table）中所有包含依赖外键值（dependent foreign key value）得数据行也将被删除。例如，当父表（parent table）的一行数据被删除时，如果此行的主键值被子表中一个或多个外键值引用，则子表中引用了此主键值的所有数据行也将被删除。

置空

置空（set null）选项的含义是：如果包含引用键值（referenced key value）的数据行被删除，则子表（child table）中所有依赖外键值（dependent foreign key value）将被置空。例如，在TMP 表中 employee_id 列引用了manager_id 列，删除一条经理数据时，所有为此经理工作的员工的manager_id 值将被置空。

引用操作对 DML 的限制

采用不同引用操作（referential action）选项时，对父表（parent table）的主键值（primary key value）/唯一键值（unique key value）及子表（child table）的外键值（foreign key value）可以执行的 DML 语句。

DML 语句	对父表执行	对子表执行
INSERT	当父键值唯一时允许执行。	当外键值能与一个父键值匹配，或外键值部分或全部为空时允许执行。
UPDATE No Action	如果更新操作不会使子表内的任何数据行没有可引用的父键值，则允许执行。	如果新外键值依旧能与一个父键值匹配，则允许执行。
DELETE No Action	如果子表没有引用此主键值则允许执行。	允许执行。
DELETE Cascade	允许执行。	允许执行。
DELETE Set Null	允许执行。	允许执行。

并发控制，索引，及外键

在大多数情况下，用户需要在外键（foreign key）上创建索引。但有一种情况例外，当外键所引用的主键（primary key）或唯一键（unique key）从不更新或删除时，外键上可以不创建索引。
Oracle 负责进行并发控制（concurrency control），确保数据访问时父键（parent key）及依赖外键（dependent foreign key）之间关系的正确性。用户可以根据实际情况选择并发控制的机制。以下各节将介绍不同情况，及在各种情况下 Oracle 建议的并发控制机制。

外键上无索引

如果外键（foreign key）上没有定义索引，当父表（parent table）中的数据行被更新或删除时，Oracle 采用的锁机制（locking mechanism）如图 21-8 所示。而向父表中插入数据时无需对子表（child table）加锁。

如果外键上没有定义索引，对父表的主键执行 DML 操作时必须获得子表上的共享行排他表级锁（share row exclusive table lock）（也称为share-subexclusive table lock，SSX）。此锁能够阻止其他事务对子表执行 DML 操作。SSX 锁在获得后立即释放。如果父表中有多个主键值被更新或删除，对每行执行 DML 操作时都需要对子表进行一次加锁及解锁操作。

如果外键上定义了索引，则对父表的主键执行 DML 操作时只需获得子表上的行共享表级锁（row share table lock）（也称为subshare table lock，SS）。此锁不允许其他事务排他地对子表加锁，但允许对父表及子表执行 DML 操作。

外键上有索引
如果外键（foreign key）上定义了索引，当插入，更新，或删除子表（child table）中的数据行时采用的锁机制如 图 21-9 所示。
如果外键上定义了索引，事务需要获得父表（parent table）及子表上的行共享表级锁（row share table lock）（也被称为 subshare table lock，SS）。此锁能够阻止其他事务排他地对表加锁，但允许对表执行 DML 操作
如果对子表进行更新操作的同时有可能存在对父表的更新或删除操作，则适宜采取本节讲述的锁机制。对父表的插入，更新，及删除操作无需获得子表上的锁，但更新及删除操作需要等待子表索引上的行级锁。

如果子表的外键约束定义中指定了 ON DELETE CASCADE 选项，则删除主表数据时将导致相关的子表数据同时被删除。在这种情况下，Oracle 采用的锁机制与用户先手工地删除主表数据，再删除相关的子表数据时采用的锁机制相同。

CHECK 完整性约束

CHECK 完整性约束（integrity constraint）定义于列或列集上，此约束要求数据行满足用户定义的检查条件（或条件判断结果不确定（unknown））。如果一个 DML 语句使 CHECK 完整性约束的检查结果为假（false），则此语句将被回滚（rolled back）。
检查条件

用户可以使用 CHECK 约束定义检查条件（check condition）来实现特殊的完整性规则（integrity rule）。定义CHECK 约束的检查条件时有以下限制

• 检查条件必须是用于评估被插入或被更新数据行内的值的布尔表达式（Boolean expression）。

• 检查条件中不能包含：子查询（subquery）；序列（sequence）；SYSDATE，UID，USER，及USERENV 等 SQL 函数；LEVEL 或ROWNUM虚列（pseudocolumn）。

如果 CHECK 约束的检查条件中包含预定义字符串（string literal）或使用全球化支持参数（globalization support parameter）的 SQL 函数（例如TO_CHAR，TO_DATE，及TO_NUMBER），Oracle 默认使用数据库的全球化支持设置。用户也可以在定义 CHECK 约束的检查条件时，显式地设定全球化支持参数。
多重 CHECK 约束

同一列可以被多个 CHECK 约束（constraint）的条件定义所引用。用户为某一列定义的CHECK 约束的数量不受限制。
如果用户为在一列上创建了多个 CHECK 约束，必须确保各个约束的检查条件不会相互冲突。CHECK 约束的检查顺序是不确定的。Oracle 也不会检查各个CHECK 约束是否为互斥的（mutually exclusive）。

延迟约束检查

用户可以将约束检查（checking constraint for validity）延迟（defer）至事务结束时进行。

• 如果 Oracle 在事务提交（commit）时才对约束执行检查，则称此约束是延迟的（deferred）。如果数据违反了延迟约束，提交操作将导致事务被回滚（undo）。

• 如果约束是即时的（immediate）（非延迟的），则此约束将在语句执行结束后进行检查。如果数据违反了延迟约束，语句将被立即回滚。

如果在约束中定义了操作（action）（例如，串联删除（delete cascade）），这些操作将被视为导致此操作的语句的一部分，无论约束是延迟的或即时的。

约束属性

用户可以将约束定义为可延迟的（deferrable）或不可延迟的（not deferrable），以及初始为延迟的（initially deferred）或初始为即时的（initially immediate）。上述属性应与不同类型的约束结合使用。用户可以在CONSTRAINT 子句中使用以下关键字进行设定：

• DEFERRABLE 或NOT DEFERRABLE

• INITIALLY DEFERRED 或INITIALLY IMMEDIATE

约束可以被添加（add），移除（drop），启用（enable），禁用（disable），或验证（validate）。用户还可以修改约束的各种属性。

约束状态

• ENABLE（启用）确保所有输入的数据都遵从约束（constraint）

• DISABLE（禁用）总是允许输入数据，无论数据是否遵从约束

• VALIDATE（验证）确保已存在的数据遵从约束

• NOVALIDATE（无验证）允许已存在的数据不遵从约束

此外：

• ENABLE VALIDATE 与ENABLE相同。Oracle 将检查约束，并保证所有数据均遵从约束。

• ENABLE NOVALIDATE 表示 Oracle 将检查约束，但不保证所有数据均遵从约束。这确保了所有新插入或被修改的数据遵从约束，但允许已存在的数据不遵从约束。
  
  在 ALTER TABLE 语句中，使用 ENABLE NOVALIDATE 子句将恢复约束检查，但不会验证表中已存在的数据。

• DISABLE NOVALIDATE 与DISABLE相同。Oracle 不会检查约束，也不保证所有数据均遵从约束。

• DISABLE VALIDATE 将禁用约束，移除约束使用的索引，并禁止修改约束键的数据。
  
  用户在使用 ALTER TABLE 语句的 EXCHANGE PARTITION 子句将非分区表（nonpartitioned table）数据加载到分区表（partitioned table）时，如果存在UNIQUE 约束，用户可以使用DISABLE VALIDATE 来提高数据加载的效率。

在上述状态间进行转换时存在以下规则：

• ENABLE 即隐含着VALIDATE，除非设定了 NOVALIDATE。

• DISABLE 即隐含着NOVALIDATE，除非设定了 VALIDATE。

• VALIDATE 与NOVALIDATE 默认不隐含 ENABLE 与DISABLE。

• 当一个唯一键约束（unique key）或主键约束（primary key）从DISABLE 转换为 ENABLE 状态时，如果没有索引存在，Oracle 将自动地创建一个唯一索引（unique index）。类似地，当一个唯一键约束或主键约束从ENABLE 转换为DISABLE 状态时，如果约束是通过唯一索引实现的，那么此索引将被移除。

• 任意约束从 NOVALIDATE 转换为VALIDATE 状态时，所有数据均需进行约束检查。（此过程可能很慢。）将约束从VALIDATE 转换为 NOVALIDATE 状态时无需任何检查。

• 将一个约束从 ENABLE NOVALIDATE 转换为ENABLE VALIDATE 状态时，转换语句不会阻塞（block）读取，写入，或其他 DDL 语句。转换操作可以被并行执行。