Oracle 19c Concepts(05)：Data Integrity

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

只有在表不包含任何行或指定默认值的情况下，才能给列添加NOT NULL约束。

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

如下例所示，emp_email_uk 约束可以确保没有任何两名雇员具有相同的电子邮件地址。

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)    
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)    
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)    
  1  VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

• 有时，当您使用一个可延迟的约束选项来创建一个主键时，其生成的索引不是唯一的。

• 当你创建主键约束时，如果有一个现成的索引可用，则该主键约束会重用此索引，而不会隐式创建一个额外的新索引。

Foreign Key 约束

只要两个表包含一个或多个公共列， 则数据库可以通过一个外键约束（也称为参照完整性约束）来强制建立两个表之间的关系。

该约束要求定义约束的列中的每个值， 必须与另一个指定表中的指定列中的值相匹配。参照完整性规则的一个例子是， 雇员只可以为一个现有的部门工作。

下表列出了与参照完整性约束相关的术语。

表 5-2 参照完整性约束术语

术语	定义
Foreign key	约束定义中包含的列或列集， 它引用了参考键。 例如，employees 表中的 department_id 列是一个外键，它引用了 departments 表中的 department_id 列。 外键可以定义在多个列上。但是， 复合外键必须引用具有相同数量和相同数据类型列的复合主键或复合唯一键。 外键的值， 可以要么匹配被引用主键或唯一键的值， 要么为空。 如果一个复合外键的任何列为空，则该键的非空部分不一定要匹配父项中的任何相应部分。
Referenced key	被外键所引用的表中的唯一键或主键。 例如，相对于 employees 表中的 department_id 列来说，departments 表中的 department_id 列是被引用键。
Dependent or child table	包含外键的表。此表依赖于父表中被引用的唯一键或主键的值。 例如， employees 表是 departments 表的一个子表。
Referenced or parent table	由子表的外键引用的表。正是该表中的被引用值决定了在子表中特定的插入或更新是否可被允许。 Departments 表是 employees 表的父表。

图 5-1 显示在 employees.department_id 列上的一个外键。它保证在此列中的每个值必须与 departments.department_id 列中的某个值相匹配。因此， 在 employees.department_id 列中不会存在错误的部门编号。

Figure 5-1 Referential Integrity Constraints

Description of "Figure 5-1 Referential Integrity Constraints"

另见

《Oracle Database 2 Day Developer's Guide》 and 《Oracle Database Development Guide》了解如何向表中添加外键约束

自引用完整性约束

自引用完整性约束是一个引用同一个表中的父键为外键的约束

在下图中, 自引用约束可以确保在 employees.manager_id 列中的每个值对应于 employees.employee_id 列中的一个现有值。例如， 员工 102 的经理必须存在于 employees 表中。此约束消除了在 manager_id 列中存在错误的雇员人数的可能性。

图 5-2 单表引用约束

Description of "Figure 5-2 Single Table Referential Constraints"

空值和外键

关系模型允许外键的值可以匹配被引用主键或唯一键值，或者为空。例如，用户可以往 hr.employees 表插入行 ， 而无需指定一个部门 id。

如果一个复合外键的任何列为空，则该键的非空部分不一定要匹配父项中的任何相应部分。例如，一个 reservation 表可能在 table_id 和 date 列上包含一个复合外键，但是table_id是null。

父键修和外键

删除父键会影响外键和父键之间的关系。例如， 如果用户试图删除某个部门的记录， 那么属于这个部门的雇员记录会发生事情呢？

在父键被修改时，参照完整性约束可以指定在子表中的相关行上，执行以下某种操作之一：

• 对删除或更新操作，不采取任何动作

  在正常的情况下，如果结果会违反参照完整性， 用户不能修改被引用的键值。例如， 如果 employees.department_id 是对 departments 表的一个外键， 且有一些雇员属于某个特定部门，则试图删除该部门所在的行会违反约束。

• 级联删除

  级联删除(DELETE CASCADE)即是当包含被引用键值的行被删除时，导致子表中的所有的外键依赖值所在行也会被删除。例如，删除departments 表中的某行， 会导致这个部门中的所有雇员行也被删除。

• 对删除置空

  删除置空(DELETE SET NULL)即是当包含被引用键值的行被删除时，导致子表中的所有的外键依赖值被全部置空。 例如，删除部门表中的某行， 会导致将该部门中的所有雇员行的 department_id 列被置为空。

表 5-3 列出了在父表中的键值及子表中的外键值上，针对不同的引用性操作，所允许的 DML 语句。

表 5-3 Update 和 Delete No Action 所允许的 DML 语句

DML 语句	对父表发出	对子表发出
INSERT	若该父键值是唯一的，则总是可以	只有当外键值存在相应父键、 或部分为空、 或全为空时才可以
UPDATE NO  ACTION	只要该语句在子表中不留下任何这样的行——其外键值在父表中不存在，则允许	只要新的外键值仍可以引用父表中的某个被引用键值，则允许
DELETE NO  ACTION	只要子表中没有行引用该父键值，则允许	总是可以
DELETE CASCADE	总是可以	总是可以
DELETE SET  NULL	总是可以	总是可以

另见：

《Oracle Database SQL Language Reference》了解 ON DELETE 子句

索引和外键

作为一条规则——总是应该为外键编制索引。唯一的例外是， 当匹配的唯一键或主键永远不会被更新或删除时。

在子表中的外键上建立索引提供了下列好处：

• 可防止在子表上的全表锁定。相反，数据库只需要在索引上获取一个行锁。

• 消除在子表上进行全表扫描的需要。作为一个说明， 假定用户从部门表中删除部门 10 的记录。如果没有为 employees.department_id 建立索引， 则数据库必须扫描 employees 表， 以查看是否存在属于部门 10 的雇员。

另见：

• "Locks and Foreign Keys"解释索引和未索引外键列的锁定行为

• "Introduction to Indexes"说明指标的用途和特点

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

完整性约束的状态

作为约束定义的一部分，您可以指定数据库应如何及何时强制执行该约束条件， 从而确定约束状态。

对现有数据和新数据的检查

数据库使您可以指定将某个约束应用于现有数据还是应用于新数据。如果约束是启用的，则当输入或更新数据时， 数据库会检查新的数据。不符合该约束的数据不能输入到数据库。 

例如， 对 employees.department_id 启用 NOT NULL 约束， 可以保证新插入的每一行都有一个部门 id。如果约束是禁用的，则表可能会包含违反约束的行。

您可以将约束设置为以下两种验证模式之一：

• 验证

  现有数据必须符合约束。例如，在employees.department_id上启用NOT NULL约束，并将其设置为VALIDATE检查现有的每一行是否具有部门ID。

• 不验证

  现有数据不需要符合约束。实际上，这是一种“相信我”的模式。例如，如果您确定加载到表中的每个sales都有一个日期，那么您可以在date列上创建一个NOT NULL约束，并将该约束设置为NOVALIDATE。非强制约束通常只对物化视图和查询重写有用。

  对于NOVALIDATE模式下的约束，RELY 参数表明优化器可以使用该约束来确定连接信息。尽管约束不用于验证数据，但它支持对物化视图进行更复杂的查询重写，并支持数据仓库工具从数据字典检索约束信息。默认值为NORELY，这意味着优化器实际上不知道约束。

VALIDATE 和 NOVALIDATE 的行为都取决于约束是启用的还是禁用的。下表总结了这些选项组合。

Table 5-4 Checks on Modified and Existing Data

Modified Data	Existing Data	Summary
ENABLE	VALIDATE	现有的和将来的数据都必须遵守约束。如果试图在一个已填充的表上应用一个新的约束，而现有的行违反该约束，则会导致一个错误。
ENABLE	NOVALIDATE	数据库会检查该约束，但不需要对所有行为真。因此，现有的行可以违反该约束，但新的或修改后的行必须遵守该约束。这种模式通常用于包含已验证完整性的现有数据的数据仓库。
DISABLE	VALIDATE	数据库禁用该约束、 删除其上的索引，并防止修改受约束的列。
DISABLE	NOVALIDATE	不检查约束，也不需要为真。

另见：

《Oracle Database SQL Language Reference》了解约束状态

当数据库检查约束的有效性时

约束可能处于不可延迟（默认值） 或可延迟两种状态之一。该状态确定数据库何时检查约束的有效性。

下图描述了可延迟约束的选项。

图 5-3 可延迟约束的选项

Description of "Figure 5-3 Options for Deferrable Constraints"

不可延迟约束

如果一个约束不可延迟，则 Oracle 数据库决不会将约束的有效性检查延迟到事务结束。相反，数据库在每个语句的结尾检查约束。如果违反了约束，则该语句被回滚。

例如， 假设您在 employees.last_name 列上创建一个不可延迟的 NULL 约束。如果用户试图插入一个没有姓氏的行， 则数据库会立即回滚该语句， 因为违反了 NOT NULL 约束。 在这种情况下， 将不会插入任何行。

可延迟约束

可延迟约束允许事务使用 SET CONSTRAINT 子句将约束检查推迟到发出 COMMIT 语句时。如果你对数据库中做了可能违反约束的更改，则此设置让您有效地禁用该约束，直到完成所有更改。

您可以设置数据库检查可延迟约束时的默认行为。您可以指定下列属性之一：

• INITIALLY IMMEDIATE

  数据库在每个语句执行后，立即检查约束。如果违反了约束， 则数据库回滚该语句。

• INITIALLY DEFERRED

  发出 COMMIT 时，数据库检查约束。如果违反了约束，则数据库回滚该事务。

假定一个在 employees.last_name 列上的可延迟的 NOT NULL 约束被设置为 INITIALLY DEFERRED。用户创建了一个包含 100 个 INSERT 语句的事务， 其中一些在 last_name 列上具有空值。当用户尝试提交时，数据库将回滚所有 100 个语句。但是，如果这种限制被设置为 INITIALLY IMMEDIATE， 则数据库将不会回滚整个事务。 

如果一个约束导致一个动作， 则数据库认为此动作是引发它的语句的一部分， 而无论该约束是可延迟的或立即的。 例如，从 departments 表中删除一行，会导致删除 employees 表中引用了相应的被删部门的雇员。在这种情况下， 从 employees 表中删除雇员被认为是对 departments 表执行的 DELETE 语句的一部分。

另见：

《Oracle Database SQL Language Reference》关于约束属性及其默认值的信息

约束检查的示例

下面的例子可以帮助说明数据库何时执行检查约束。

假定如下：

• employees表具有 "Self-Referential Integrity Constraints" 中所示的结构。

• 自引用约束要求在 manager_id 列中的条目依赖 employee_id 列的值。

例子：当父键值不存在时，往外键列中插入值

考虑向 employees 表中插入第一行的情况。 由于当前不存在任何行，那么如果 manager_id 列中的值不能引用在 employee_id 列中的任何现有值，如何才能输入新行呢？

一些可能情况是：

• 如果 在 manager_id 列上没有定义 NOT NULL 约束，可以为第一行中的 manager_id 列输入一个空值。

  因为在外键中允许使用空值， 此行可以被插入到表中。

• 如果该雇员是他或她自己的经理， 可以在 employee_id 和 manager_id 列中输入相同的值。
  这种情况表明数据库是在完全运行该语句之后，执行其约束检查的。若要允许输入在父键和外键中具有相同值的行，数据库必须首先运行该语句 （也就是插入新的行），然后再确定表中是否有任何行其 employee_id 对应新行的 manager_id 列。

• 一个多行的 INSERT 语句， 如一个具有嵌套 SELECT 语句的 INSERT 语句， 可以插入相互引用的多个行。
  例如， 第一行可能具有雇员 ID 200 和经理 ID 300，而第二行的雇员 ID 300 和经理 ID 200。约束检查被推迟到整个语句执行完成。首先， 所有行先被插入， 然后对所有行进行违反约束检查。

在语句进行解析之前， 其默认值作为 INSERT 语句的一部分被包括在内。因此， 默认列值都要遵从所有完整性约束检查。

例：同时更新外键和父键的值

在这个例子中，自引用约束使员工的manager_id列中的条目依赖于employee_id列的值。

假如该公司已出售。由于这次出售， 所有雇员编号必须都更新为其当前值加 5000，以适应新公司的员工编号。如下图所示，部分员工同时也是管理员：

图 5-4 更新前的雇员表

Description of "Figure 5-4 The employees Table Before Updates"

因为经理编号是真正雇员编号，经理编号也必须增加 5000。您可以执行下面的 SQL 语句，以更新其值：

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

图 5-5 约束检查

Description of "Figure 5-5 Constraint Checking"