数据完整性

本章介绍完整性约束条件如何强制实施与数据库关联的业务规则，并防止在表中输入无效信息。

数据完整性简介

重要的是，数据应保持数据完整性，这符合由数据库管理员或应用程序开发人员确定的业务规则。

业务规则指定必须始终为真或必须始终为假的条件和关系。例如，每个公司定义自己的薪金、员工编号、库存跟踪等政策。

保证数据完整性的技术

在设计数据库应用程序时，开发人员有几个选项来保证数据库中存储的数据的完整性。

这些选项包括：

另请参见：

完整性约束的优点

完整性约束条件是使用 SQL 创建和删除的方案对象。要强制实施数据完整性，请尽可能使用完整性约束条件或断言。

与实施数据完整性替代方案相比，完整性约束条件和断言的优点包括：

另请参见：

完整性约束条件类型

借助 Oracle AI Database，您可以在表级和列级应用约束条件，以及涉及多个表的断言。

作为列或属性定义的一部分指定的约束条件是一种内嵌规范。在表定义中指定的约束条件是一种行外规范。

键是特定类型的完整性约束条件定义中包含的一个或一组列。键描述关系数据库的表与列之间的关系。关键字中的单个值称为关键字值。

下表介绍了约束条件的类型。除了必须内联的 NOT NULL 外，每个都可以指定为内联或外联。

表 1 完整性约束条件类型

约束条件类型 说明 另请参见
NOT NULL 允许或禁止在指定列中插入或更新包含 null 的行。 “ NOT NULL Integrity 约束条件”
唯一关键字 禁止多个行在相同的列或列组合中具有同样的值，但允许某些值为空。 “唯一约束”
主键 组合 NOT NULL 约束条件和唯一约束条件。它禁止多个行在同一列或列的组合中具有相同的值，并禁止将值设为 Null。 Primary Key Constraints（主键约束条件）
外关键字 将列指定为外键，并在外键与主键或唯一键（称为引用键）之间建立关系。 “ Foreign Key 约束条件”
支票 需要使用数据库值来服从指定的条件。 检查约束条件
REF 指示 REF 列中值允许的数据处理类型以及这些操作如何影响相关值。在对象关系数据库中，称为 REF 的内置数据类型封装了对指定对象类型的行对象的引用。REF 列的引用完整性约束条件可确保 REF 具有行对象。 Oracle AI Database Object-Relational Developer’s Guide ，了解 REF 约束条件
Assertions 断言是布尔表达式，涉及应用程序数据库表中的子查询。断言具有约束条件的语义，也就是说，数据库的任务是在事务处理更改数据并提交这些更改时确保这些布尔表达式的真实性。 SQL 断言，了解断言

另请参见：

NOT NULL 完整性约束条件

NOT NULL 约束条件要求表的某列不包含 Null 值。null 是缺少值。默认情况下，表中的所有列都允许 Null 值。

NOT NULL 约束条件适用于不能缺少值的列。例如，hr.employees 表需要 email 列中的值。尝试插入没有电子邮件地址的员工行会生成错误：

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 约束条件的列。

另请参见：

唯一约束条件

唯一键约束条件要求一列或一组列中的每个值都是唯一的。一个表中的任何行在单个列（唯一键）或一组具有唯一键约束条件的列（组合唯一键）中都可能具有重复值。

注：术语关键字仅指完整性约束条件中定义的列。由于数据库通过在键列上隐式创建或重用 index 来强制实施唯一约束条件，因此术语唯一键有时会错误地用作唯一键约束条件或唯一索引的同义词。

唯一键约束条件适用于不允许有重复值的列。唯一约束条件与主键约束条件不同，主键约束条件的目的是唯一地标识每个表行，并且通常包含除唯一外无任何意义的值。唯一键的示例包括：

如示例 4-1 中所示，hr.employees 表的 email 列上存在一个唯一键约束条件。该声明的有关部分如下：

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

除非还定义了 NOT NULL 约束条件，否则 Null 值始终满足唯一键约束条件。因此，具有唯一键约束条件和 NOT NULL 约束条件的列是典型的。此组合将强制用户在唯一键中输入值，并消除新行数据与现有行数据冲突的可能性。

注：由于搜索机制向多个列施加了唯一键约束条件，因此部分为 NULL 组合唯一键约束条件的非 Null 列中不能有相同值。

示例 7-1 唯一约束条件

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

另请参见：

主键约束条件

在主键约束条件中，受此约束条件约束的一列或多列组中的值唯一地标识行。每个表可以具有一个主关键字，主关键字实际上用于命名行并确保不存在重复行。

主键可以是自然键，也可以是代理键。自然关键字是由表中现有属性组成的有意义的标识符。例如，自然关键字可以是查找表中的邮政编码。

相反，代理关键字是系统生成的递增标识符，可确保表中的唯一性。通常， sequence 或 UUID 生成代理密钥。

主键约束条件的 Oracle AI Database 实施可确保以下表述正确：

调用主键的典型情况是员工的数字标识符。每个雇员都必须有一个唯一的标识。一个雇员必须由 employees 表中的一行且仅一行来描述。

Unique Constraints 中的示例指明现有员工的员工 ID 为 202，其中员工 ID 是主键。以下示例显示尝试添加具有相同员工 ID 的员工和没有 ID 的员工：

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")

数据库使用 index 强制实施主键约束条件。通常，为列创建的主键约束条件会隐式创建唯一索引和 NOT NULL 约束条件。请注意此规则的以下例外情况：

默认情况下，隐式创建的索引的名称是主键约束条件的名称。还可以为索引指定用户定义的名称。通过在用于创建约束条件的 CREATE TABLE 或 ALTER TABLE 语句中包括 ENABLE 子句，可以为索引指定存储选项。

另请参阅：Oracle AI Database Get Started with Oracle AI Database Development 和Oracle AI Database Development Guide，以了解如何向表添加主键约束条件

外键约束条件

每当两个表包含一个或多个公用列时，Oracle AI Database 都会通过外键约束条件（也称为引用完整性约束条件）强制实施这两个表之间的关系。

外键约束条件要求，对于定义约束条件的列中的每个值，另一个指定的其他表和列中的值必须匹配。引用完整性规则的一个示例是员工只能为现有部门工作。

下表列出了与引用完整性约束条件关联的术语。

表 2 引用完整性约束条件术语

期限 定义
外关键字

引用引用的键的约束条件定义中包括的列或列集。例如，employees 中的 department_id 列是引用 departments 中的 department_id 列的外键。

外键可以定义为多列。但是，组合外键必须引用具有相同列数和相同数据类型的组合主键或唯一键。

外键的值可以与引用的主键值或唯一键值匹配，也可以为空。如果组合外键的任何列为 Null，则键的非 Null 部分不必与父键的任何对应部分匹配。
引用的关键字 外键所引用的表的唯一键或主键。例如，departments 中的 department_id 列是 employees 中 department_id 列的引用键。
受抚养人或子表 包含外键的表。此表取决于所引用的唯一键或主键中存在的值。例如，employees 表是 departments 的子表。
引用表或父表 子表的外键所引用的表。正是此表的引用键决定了子表中是否允许特定的插入或更新。例如，departments 表是 employees 的父表。

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

图 1：引用完整性约束条件

插图 cncpt056.gif 的说明

自我引用完整性约束条件

自引用完整性约束条件是引用同一表中的父键的外键。

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

图 2：单表引用约束条件

插图 cncpt057.gif 的说明

Null 和外键

关系模型允许外键的值与引用的主键或唯一键值匹配，或者为空。例如，hr.employees 中的行可能未指定部门 ID。

如果组合外键的任何列为 Null，则键的非 Null 部分不必与父键的任何对应部分匹配。例如，reservations 表可能包含 table_id 列和 date 列上的组合外键，但 table_id 为空值。

父键修改和外键

外键和父键之间的关系对删除父键有影响。例如，如果用户尝试删除此部门的记录，则此部门中员工的记录会发生什么情况？

修改父键时，引用完整性约束条件可以指定要对子表中的相关行执行的以下操作：

表 7-3 概述了对父表中的键值执行的不同引用操作所允许的 DML 语句，以及子表中的外键值。

表 3：更新和删除无操作允许的 DML 语句

DML 报表 根据父表发布 针对子表签发
INSERT 如果父关键字值唯一，则“始终确定” 仅当外键值存在于父键中，或者部分或全部为空时才确定
UPDATE NO ACTION 如果语句未在没有引用的父键值的情况下保留子表中的任何行，则允许 如果新外键值仍然引用引用的键值，则允许
DELETE NO ACTION 如果子表中没有行引用父键值，则允许 始终正常
DELETE CASCADE 始终正常 始终正常
DELETE SET NULL 始终正常 始终正常

注：可以使用数据库触发器强制执行 Oracle Database 的 FOREIGN KEY 完整性约束条件不支持的其他引用操作。请参阅“触发器概览”。

另请参阅：Oracle AI Database SQL Language Reference 以了解 ON DELETE 子句

索引和外键

通常，应该对外键进行索引。唯一的例外是从未更新或删除匹配的唯一键或主键。

为子表中的外键编制索引有以下好处：

另请参见：

检查约束条件

列或列集上的检查约束条件要求每行的指定条件均为 true 或未知。

如果 DML 导致约束条件的求值结果为 false，则会回退 SQL 语句。检查约束条件的主要优点是能够强制实施非常具体的完整性规则。例如，可以使用检查约束条件来强制实施 hr.employees 表中的以下规则：

以下示例在 employees 上创建最高薪金约束条件，并演示语句尝试插入的薪金超过最大值的行时会发生什么情况：

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

一个列可以有多个检查约束条件，这些约束条件将在其定义中参考该列。例如，salary 列可以有一个用于防止值超过 10000 的约束条件和一个用于防止值小于 500 的单独约束条件。

如果一个列存在多个 CHECK 约束条件，则必须设计这些约束条件，以使其目的不发生冲突。无法假定条件的评估顺序。数据库不会验证检查条件是否不是互斥的。

另请参阅：Oracle AI Database SQL Language Reference 以了解有关检查约束条件的限制

可预检查的 CHECK 约束条件

可以在数据库外部检查标记为 PRECHECK 的检查约束条件。

在创建或更改表时，将检查约束条件设置为 PRECHECK 状态。PRECHECK 状态是对约束条件的 ENABLE 和 VALIDATE 状态的补充，并且可以使这三种状态同时处于活动状态。

如果您使用 JSON 来表示检查约束条件，则 Oracle AI Database 允许您导出 JSON 方案并根据应用程序客户端中的 JSON 方案验证器验证 JSON 数据。将约束设置为 PRECHECK 状态时，指示约束具有等效的 JSON 方案，该方案保留约束的语义。客户机应用程序开发人员可以在客户机应用程序中预先验证约束条件。数据库将检查约束条件，如果无法在 JSON 方案中表示约束条件，则会生成错误。

相关主题

断言

断言是布尔表达式，涉及应用程序数据库表中的子查询。

断言具有约束条件的语义，也就是说，数据库的任务是在事务处理更改数据并提交这些更改时确保这些布尔表达式的真实性。

以下 SQL 语句将创建一个名为 NO_TRAINERS_IN_BOSTON 的断言，要求在位于波士顿的部门中不雇用培训师。顶层 NOT EXISTS 表示布尔表达式，该表达式要求 EMP 和 DEPT 表之间的联接结果集保持为空。

CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
   (SELECT 'trainer in Boston'
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
      AND  e.job = 'TRAINER'
      AND  d.loc = 'BOSTON'));

断言是方案级对象。这意味着，如果布尔表达式引用了其他方案的表，则需要具有 ASSERTION REFERENCES 对象权限才能访问这些表。这与跨两个方案的外键没有什么不同，即必须具备 REFERENCES 对象权限才能成功创建外键，该外键将“指向”其他方案中的表的主键。

断言是通用数据完整性约束条件。它们可用于替换 CHECK、PRIMARY KEY、UNIQUE 和 FOREIGN KEY 约束条件。

例如，对 EMP 表假定以下约束条件：

可以使用以下断言实现这些约束条件：

CREATE ASSERTION commission_not_greater_than_salary CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  e.comm > e.sal));
CREATE ASSERTION emp_empno_pk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.empno = e2.empno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_ename_deptno_uk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.ename = e2.ename
     AND  e1.deptno = e2.deptno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_fk_dept CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  NOT EXISTS
     (SELECT ''
      FROM   dept d
      WHERE  d.deptno = e.deptno)));

但是，我们强烈建议您始终尽可能使用约束条件，并且仅在无法使用约束条件时使用断言。

相关主题

完整性约束条件和断言的状态

作为约束条件或断言定义的一部分，可以指定 Oracle AI Database 应如何以及何时强制实施约束条件或断言，从而确定约束条件或断言状态。

检查已修改数据和现有数据

使用数据库可以指定约束条件或断言是应用于现有数据还是将来的数据。如果启用了约束条件或断言，则数据库将在输入或更新新数据时对其进行检查。不符合约束条件或断言的数据无法进入数据库。

例如，对 employees.department_id 启用 NOT NULL 约束条件可以保证每个将来的行都有一个部门 ID。如果禁用了约束条件，则表可以包含违反约束条件的行。

可以将约束和断言设置为以下任一验证模式：

VALIDATE 和 NOVALIDATE 的行为始终取决于是启用还是禁用约束条件或断言。下表汇总了这些关系。

表 4 检查已修改数据和现有数据

修改的数据 现有数据 汇总
ENABLE VALIDATE 现有数据和未来数据必须遵守约束条件或断言。如果现有行违反约束条件或断言，尝试对已填充的表应用新约束条件或断言将会导致错误。
ENABLE NOVALIDATE 数据库将检查约束条件或断言，但并非所有行都为真。因此，现有行可能会违反约束条件或断言，但新行或修改的行必须符合规则。此模式通常用于包含已验证完整性的现有数据的数据仓库。
DISABLE VALIDATE 数据库会禁用约束条件，删除其索引，并防止修改受约束的列。断言不能处于此状态。
DISABLE NOVALIDATE 不检查约束条件或断言，也不一定为真。

另请参阅：《 Oracle AI Database SQL Language Reference 》以了解约束条件状态

数据库检查约束条件和断言是否有效时

每个约束条件和断言要么处于不可延迟（默认）状态，要么处于可延迟状态。此状态确定 Oracle AI Database 何时检查约束条件或断言的有效性。

下图显示了可延迟约束和断言的选项。

图 3：可延迟约束和断言的选项

插图 cncpt313.gif 的说明

不可推断的约束条件和断言

在不可转移约束条件中，Oracle AI Database 绝不会将约束或断言的有效性检查推迟到事务处理结束时。相反，数据库会检查每个语句末尾的约束条件或断言。如果违反了约束条件或断言，则语句将回退。

例如，employees.last_name 列存在不可推断的 NOT NULL 约束条件。如果会话尝试插入没有姓氏的行，则数据库会立即回退该语句，因为违反了 NOT NULL 约束条件。不插入任何行。

可延迟的约束条件和断言

可延迟的约束条件或断言允许事务处理使用 SET CONSTRAINT 子句来延迟检查此约束条件或断言，直到发出 COMMIT 语句。如果对数据库进行可能违反约束条件或断言的更改，则此设置可以有效地禁用约束条件或断言，直到所有更改都完成。

您可以为数据库检查可推迟的约束条件或断言时设置默认行为。可以指定以下任一属性：

假设 employees.last_name 上的可延迟 NOT NULL 约束条件设置为 INITIALLY DEFERRED。用户创建具有 100 条 INSERT 语句的事务处理，其中一些语句的 last_name 值为空。当用户尝试提交时，数据库将回退所有 100 条语句。但是，如果将此约束条件设置为 INITIALLY IMMEDIATE，则数据库不会回退事务处理。

如果约束条件导致了一个操作，则数据库将此操作视为导致该操作的语句的一部分，无论此约束条件是延迟还是立即执行的。例如，删除 departments 中的行会导致删除 employees 中引用已删除部门行的所有行。在这种情况下，从 employees 删除被视为对 departments 执行的 DELETE 语句的一部分。

另请参阅： Oracle AI Database SQL Language Reference，了解有关约束条件属性及其默认值的信息

约束条件和断言检查示例

以下示例有助于说明 Oracle AI Database 何时检查约束条件和断言。

假设：

示例：不存在父键值时在外键列中插入值

此示例涉及将第一行插入 employees 表。当前不存在行，因此，如果 manager_id 列中的值不能引用 employee_id 列中的现有值，如何输入行？

有以下几种可能：

在对语句进行语法分析之前，默认值将包含在 INSERT 语句中。因此，默认列值受所有完整性约束条件检查的约束。

示例：更新所有外键和父键值

在此示例中，自引用约束条件使 employees 的 manager_id 列中的条目依赖于 employee_id 列的值。

公司已被出售。由于此销售，必须将所有员工编号更新为当前值加上 5000，以便与新公司的员工编号协调。如下图所示，一些雇员也是经理：

图 4：更新前的员工表

插图 cncpt058.gif 的说明

由于经理编号也是员工编号，因此经理编号也必须增加 5000。可以执行以下 SQL 语句来更新值：

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

虽然定义了一个约束条件来验证每个 manager_id 值是否与一个 employee_id 值匹配，但前面的语句是有效的，因为数据库在语句完成后会有效地检查约束条件。图 7-5 显示，在检查约束条件之前，数据库将执行整个 SQL 语句的操作。

图 5：约束检查

插图 cncpt059.gif 的说明

本节中的示例说明了 INSERT 和 UPDATE 语句中的约束条件检查机制，但数据库对所有类型的 DML 语句使用相同的机制。数据库对所有类型的约束条件使用相同的机制，而不仅仅是自引用约束条件。注：对视图或同义词的操作受基表中定义的完整性约束条件约束。

示例：每个经理必须在同一部门有一名职员

本示例创建一个断言，以确保每个经理在同一部门都有一名职员。

此示例要求您先插入职员，然后再插入经理。执行多行插入时，与约束一样，会在执行每个 INSERT 语句后验证断言。您可以将此断言设置为 DEFERRABLE，这样可以先插入经理，然后再插入职员。将在 COMMIT 而不是每个 INSERT 语句处进行验证。

CREATE ASSERTION manager_has_clerk check
(NOT EXISTS
  (SELECT 'manager without clerk'
   FROM   employees m
   WHERE  m.job_id = 'ST_MAN'
     AND  NOT EXISTS
            (SELECT 'a clerk in same department'
             FROM   employees c
             WHERE  c.job_id = 'ST_CLERK'
               AND  c.department_id = m.department_id)));