This chapter explains how to use integrity constraints to enforce the business rules associated with your database and prevent the entry of invalid information into tables.
本章讲述如何通过完整性约束（integrity constraint）来确保数据遵从业务规则（business rule）的要求，并防止数据表中出现无效数据。
Introduction to Data Integrity

Overview of Integrity Constraints

Types of Integrity Constraints

The Mechanisms of Constraint Checking

Deferred Constraint Checking

Constraint States

本章包含以下主题：

• 数据完整性简介
• 完整性约束概述
• 完整性约束的类型
• 约束检查的机制
• 延迟约束检查
It is important that data adhere to a predefined set of rules, as determined by the database administrator or application developer. As an example of data integrity, consider the tables employees and departments and the business rules for the information in each of the tables, as illustrated in Figure 21-1 .
数据库内的数据必须遵从一套预定义的规则，此规则是由数据库管理员或应用程序开发者确定的。 图 21-1 展示了一个关于数据完整性（data integrity）的例子，其中定义了 employees departments 两个表，同时描述了这两个表存储信息时应遵从的业务规则。

• DEPTNO

Each row must have a value for the ENAME column. Each row must have a value for the EMPNO column, and the value must be unique. Each value in the DEPTNO column must match a value in the DEPTNO column of the DEPT table. Each value in the SAL column must be lower than 10,000. Note that some columns in each table have specific rules that constrain the data contained within them.
从图中可以看出，两个表的某些列上存在一定的规则，对存储于其中的数据起到了约束作用。
This section describes the rules that can be applied to table columns to enforce different types of data integrity.
本节讲述可以应用于数据表列上的规则，以及规则所实现的数据完整性（data integrity）的类型。
A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.
空规则 （null rule）是定义在某一列上的规则，其作用是允许或禁止将要被插入或更新的数据行此列的值为空值（null），即没有值。
A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).
唯一值规则 （unique value rule）是定义在某一列（或某一列集）上的规则，其作用是确保将要被插入或更新的数据行此列（或列集）的值是唯一的。
A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.
主键值规则 （primary key value rule）是定义在某一键（key）（键指一列或一个列集）上的规则，其作用是确保表内的每一数据行都可以由某一个键值唯一地确定。
A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value ).
引用完整性规则 （referential integrity rule）是定义在某一键（key）（键指一列或一个列集）上的规则，其作用是确保任意键值都能与相关表（related table）的某一键值（即 引用值 （referenced value））相匹配。
Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent value s. The rules associated with referential integrity

Restrict: Disallows the update or deletion of referenced data.

Set to Null: When referenced data is updated or deleted, all associated dependent data is set to NULL .

Set to Default: When referenced data is updated or deleted, all associated dependent data is set to a default value.

Cascade : When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.

No Action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle uses No Action as its default action.)

在引用完整性中还包含了如下规则：对引用值可以进行哪些类型的数据操作（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 is a user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).
复杂完整性检查 （complex integrity checking）是一种用户定义的规则，针对某一列（或某一列集），其作用是依据数据行的列值来允许或禁止插入，更新，或删除此数据行。
Oracle enables you to define and enforce each type of data integrity rule defined in the previous section. Most of these rules are easily defined using integrity constraints or database triggers.
Oracle 允许用户定义并强制实现前一节描述的各类数据完整性规则（data integrity rule）。这些规则一般通过完整性约束（integrity constraint）或数据库触发器（database trigger）来定义。
An integrity constraint is a declarative method of defining a rule for a column of a table. Oracle supports the following integrity constraints:
• NOT NULL constraints for the rules associated with nulls in a column
• UNIQUE key constraints for the rule associated with unique column values
• PRIMARY KEY constraints for the rule associated with primary identification values
• FOREIGN KEY constraints for the rules associated with referential integrity. Oracle supports the use of FOREIGN KEY integrity constraints to define the referential integrity actions, including:
  
• Update and delete No Action
• Delete CASCADE
• Delete SET NULL
• CHECK constraints for complex integrity rules
完整性约束（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）
• 删除 串联操作 （Delete CASCADE ）
• 删除 置空 （Delete SET NULL ）
• CHECK （检查）约束，强制实现复杂完整性规则（complex integrity rule）
You cannot enforce referential integrity using declarative integrity constraints if child and parent tables are on different nodes of a distributed database. However, you can enforce referential integrity in a distributed database using database triggers (see next section). 提示：

如果子表（child table）及父表（parent table）位于分布式数据库系统（distributed database）的不同节点（node）上，则无法通过声明的完整性约束（declarative integrity constraint）强制实现引用完整性（referential integrity）规则。此种情况下可以使用数据库触发器（详见下节）。 Oracle also lets you enforce integrity rules with a non-declarative approach using database triggers (stored database procedures automatically invoked on insert, update, or delete operations).
用户可以使用数据库触发器（database trigger）（一种数据库存储过程，在发生插入，更新，或删除操作时自动地被调用）强制实现完整性规则（integrity rule），这是一种无需显式声明（non-declarative）的方式。
Oracle uses integrity constraints to prevent invalid data entry into the base tables of the database. You can define integrity constraints to enforce the business rules you want to associate with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, then Oracle rolls back the statement and returns an error.
Oracle 使用完整性约束（integrity constraint）防止用户向数据库的基表中插入无效数据。完整性约束的作用是确保数据库内存储的信息遵从一定的业务规则（business rule）。如果 DML 语句的执行结果违反了完整性约束，Oracle 将回滚语句并返回错误消息。
For example, assume that you define an integrity constraint for the salary column of the employees table. This integrity constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, then Oracle rolls back the statement and returns an information error message.
例如，用户在 employees 表的 salary 列上定义了完整性约束。此完整性约束规定 salary 列的数字值大于 10,000 的数据行不能插入 salary 表。如果某个 INSERT UPDATE 语句违反了此完整性约束，Oracle 将回滚语句并返回错误消息。
The integrity constraints implemented in Oracle fully comply with ANSI X3.135-1989 and ISO 9075-1989 standards.
Oracle 实现的完整性约束完全符合 ANSI X3.135-1989 及 ISO 9075-1989 标准。
This section describes some of the advantages that integrity constraints have over other alternatives, which include:
• Enforcing business rules in the code of a database application
• Using stored procedures to completely control access to data
• Enforcing business rules with triggered stored database procedures
本节描述完整性约束（integrity constraint）与其他解决方案相比的优势，其他解决方案包括：
• 在数据库应用程序代码中强制实现业务规则（business rule）
• 在存储过程（stored procedure）内实现对数据存取的控制
• 使用触发执行的（triggered）存储过程强制实现业务规则
Define integrity constraints using SQL statements. When you define or alter a table, no additional programming is required. The SQL statements are easy to write and eliminate programming errors. Oracle controls their functionality. For these reasons, declarative integrity constraints are preferable to application code and database triggers. The declarative approach is also better than using stored procedures, because the stored procedure solution to data integrity controls data access, but integrity constraints do not eliminate the flexibility of ad hoc data access.
完整性约束（integrity constraint）可以使用 SQL 语句定义。当用户创建或修改数据表时，无需额外的编程工作就能够定义完整性约束。SQL 语句与编写程序代码相比易于实现，且不会出现编程错误。所有确保完整性的工作由 Oracle 实现。因此，声明完整性约束比使用应用程序代码或数据库触发器更为简单可靠。完整性约束与存储过程相比也具备优势，虽然存储过程能够通过控制数据存取来确保数据完整性，但降低了随机数据存取（ad hoc data access）的灵活性。
Integrity constraints are defined for tables (not an application) and are stored in the data dictionary. Any data entered by any application must adhere to the same integrity constraints associated with the table. By moving business rules from application code to centralized integrity constraints, the tables of a database are guaranteed to contain valid data, no matter which database application manipulates the information. Stored procedures cannot provide the same advantage of centralized rules stored with a table. Database triggers can provide this benefit, but the complexity of implementation is far greater than the declarative approach used for integrity constraints.
完整性约束（integrity constraint）是针对数据表（而非应用程序）定义的，并存储于数据字典（data dictionary）中。任何由应用程序输入的数据必须遵从相关数据表上的完整性约束。将业务规则（business rule）集中地以完整性约束的形式实现（而非在应用程序代码中实现），无论哪个数据库应用程序存取数据，都能确保数据表中只包含有效数据。使用存储过程（stored procedure）实现数据完整性（data integrity）不具备规则集中化的优势。使用数据库触发器（database trigger）具备此优势，但实现的复杂程度远大于声明完整性约束。
If a business rule enforced by an integrity constraint changes, then the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint. In contrast, if the business rule were enforced by the code of each database application, developers would have to modify all application source code and recompile, debug, and test the modified applications.
如果业务规则（business rule）发生了变化，管理员只需要修改实现此规则的完整性约束（integrity constraint），相关的应用程序就能够自动地遵从修改后的约束。相反，如果在各个数据库应用程序代码中实现强制实现业务规则，开发人员必须修改相关的应用程序源代码并重新编译，调试，测试被修改的应用程序。
Oracle stores specific information about each integrity constraint in the data dictionary. You can design database applications to use this information to provide immediate user feedback about integrity constraint violations, even before Oracle runs and checks the SQL statement. For example, an Oracle Forms application can use integrity constraint definitions stored in the data dictionary to check for violations as values are entered into the fields of a form, even before the application issues a statement.
Oracle 能够在数据字典（data dictionary）中存储与完整性约束（integrity constraint）相关的信息。开发者可以在数据库应用程序中利用此信息即时向用户反馈违反完整性约束的情况，此种反馈甚至可以在 Oracle 检查或运行 SQL 语句之前进行。例如，Oracle Forms 应用程序可以在用户录入数据时利用数据字典中存储的完整性约束定义信息检查是否存在违反约束的情况，这种检查可以在应用程序提交语句之前进行。
The semantics of integrity constraint declarations are clearly defined, and performance optimizations are implemented for each specific declarative rule. The Oracle optimizer can use declarations to learn more about data to improve overall query performance. (Also, taking integrity rules out of application code and database triggers guarantees that checks are only made when necessary.)
完整性约束声明（integrity constraint declaration）的语义（semantic）是有明确规范的，Oracle 为每种声明规则（declarative rule）都进行性能优化（performance optimization）。Oracle 优化器（optimizer）可以利用声明信息了解数据情况，从而提高整体查询性能。（将完整性规则从应用程序代码或数据库触发器中提出能够确保这些规则只在必要时进行检查。）
You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking. the data load is complete, you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
用户可以临时地禁用完整性约束（integrity constraint），以消除加载大量数据时约束检查造成的开销（overhead）。当数据加载结束后，用户可以再启用完整性约束，Oracle 能够自动地将违反完整性约束的新数据行存储到一个独立的异常数据表（exceptions table）中。
The advantages of enforcing data integrity rules come with some loss in performance. In general, the cost of including an integrity constraint is, at most, the same as executing a SQL statement that evaluates the constraint.
使用完整性约束（integrity constraint）强制实现数据一致性规则（data integrity rule）也将带来一定的性能损失。一般来说，完整性约束造成的性能损失与进行约束检查所需执行的 SQL 语句大体相当。
You can use the following integrity constraints to impose restrictions on the input of column values:
• NOT NULL Integrity Constraints
• UNIQUE Key Integrity Constraints
• PRIMARY KEY Integrity Constraints
• Referential Integrity Constraints
• CHECK Integrity Constraints
用户可以使用以下完整性约束（integrity constraint）对输入的列值加以限制：
• NOT NULL 完整性约束
• UNIQUE 键完整性约束
• PRIMARY KEY 完整性约束
• 引用完整性约束
• CHECK 完整性约束
By default, all columns in a table allow nulls. Null means the absence of a value. A NOT NULL constraint requires a column of a table contain no null values. For example, you can define a NOT NULL constraint to require that a value be input in the last_name column for every row of the employees table.
默认情况下，表的所有列都允许为空值（null）。空值的含义是未输入值。 NOT NULL （非空）约束（constraint）要求表列内只能包含非空值。例如，用户可以在 employees 表的 last_name 列上定义 NOT NULL 约束，要求用户为每个数据行的此列输入一个值。

• DEPTNO
The ENAME column has a NOT NULL Constraint--no row may contain a null value for this column. Column COMM has an absence of a NOT NULL Constraint--any row can contain null for this column. A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique―that is, no two rows of a table have duplicate values in a specified column or set of columns.
UNIQUE key（唯一键）完整性约束（integrity constraint）要求列或列集（即键）的值唯一，数据表任意两行某列或某个列集的值不重复。
UNIQUE key constraint is defined on the DNAME column of the dept table to disallow rows with duplicate department names.
例如在 图 21-3 中， dept DNAME 列上定义了 UNIQUE 键约束，不允许此表内存在重复的部门名称。
Figure 21-3 shows a UNIQUE key constraint defined on the DNAME column of the dept table to disallow rows with duplicate department names. If a null value is entered for the DNAME column, then the row is allowed. However, if a NOT NULL constraint is also defined on the DNAME column, then the row is not allowed. 例如在 图 21-3 中，dept 表的 DNAME 列上定义了一 UNIQUE 键约束，不允许此表内存在重复的部门名称。用户向 DNAME 列中插入空值是被允许的。但如果 DNAME 列上还定义了 NOT NULL 约束，则上述数据也不允许被插入。 The columns included in the definition of the UNIQUE key constraint are called the unique key . Unique key is often incorrectly used as a synonym for the terms UNIQUE key constraint or UNIQUE index . However, note that key refers only to the column or set of columns used in the definition of the integrity constraint.
UNIQUE （唯一）键约束定义中包含的列被称为 唯一键 （unique key）。唯一键经常被错误地看作 UNIQUE 键约束 （UNIQUE key constraint）或 UNIQUE 索引 （UNIQUE index）的同义词。应该注意， 键 （key）是指完整性约束（integrity constraint）定义中所引用的列或列集。
If the UNIQUE key consists of more than one column, then that group of columns is said to be a composite unique key . For example, in Figure 21-4 the customer table has a UNIQUE key constraint defined on the composite unique key: the area and phone columns.
UNIQUE 键由多列构成，那么这组数据列被称为 复合唯一键 （composite unique key）。例如 图 21-4 所示， customer 表上定义的 UNIQUE 键约束使用了复合唯一键：其中包含 area phone 两列。
Figure 21-4 shows that the customer table has a UNIQUE key constraint defined on the composite unique key: the area and phone columns. No rows can duplicate a set of values in the key.
If a null value is entered in one of the columns, then it is allowed. However, if a NOT NULL constraint is also defined on the column, then the row is not allowed. 图 21-4 显示了 customer 表上定义的 UNIQUE 键约束使用了复合唯一键：其中包含 area 和 phone 两列。任意两行中键的值不能重复。
用户可以向列中输入空值。但是，如果列上还定义了 NOT NULL 约束，则不允许输入空值。 This UNIQUE key constraint lets you enter an area code and telephone number any number of times, but the combination of a given area code and given telephone number cannot be duplicated in the table. This eliminates unintentional duplication of a telephone number.
用户可以向 customer 表插入任意条记录，但依据上述 UNIQUE 键约束的限制，表中各行的区码（area code）与电话号码（telephone number）的组合不能重复。这能避免因疏忽造成电话号码重复问题。
Oracle enforces unique integrity constraints with indexes. For example, in Figure 21-4 , Oracle enforces the UNIQUE key constraint by implicitly creating a unique index on the composite unique key. Therefore, composite UNIQUE key constraints have the same limitations imposed on composite indexes: up to 32 columns can constitute a composite unique key.
Oracle 使用索引来强制实现唯一完整性约束（unique integrity constraint）。在 图 21-4 所示的例子，Oracle 在复合唯一键（composite unique key）上隐式地创建唯一索引（unique index），从而强制实现 UNIQUE 键约束。因此复合 UNIQUE 键约束与复合索引（composite index）一样都受以下限制：复合索引键最多由 32 列构成。
If compatibility is set to Oracle9i or higher, then the total size in bytes of a key value can be almost as large as a full block. In previous releases key size could not exceed approximately half the associated database's block size. Note:

如果数据库的兼容性被设为 Oracle9i 或更高，一个键值（key value）的容量（以字节为单位）可以与一个数据块的容量相当。而在此前的数据库版本中，一个键值的容量不能超过数据块容量的一半。 If a usable index exists when a unique key constraint is created, the constraint uses that index rather than implicitly creating a new one.
如果在创建 UNIQUE 键约束时已有可用的 UNIQUE 索引，新建约束将使用此索引，而不再隐式地创建新索引。
Figure 21-4 , UNIQUE key constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.
如 图 21-3 与 图 21-4 所示，定义了 UNIQUE 键约束的列可以输入空值（null），而同时定义了 NOT NULL 约束的列则不能输入空值。由于空值与任何值比较都无意义，因此定义了 UNIQUE 键约束而没有定义 NOT NULL 约束的列上可以包含多行值为空的记录。多行列为空值（或复合 UNIQUE 键（composite UNIQUE key）的所有列均为空值）不会违反 UNIQUE 键约束。
Columns with both unique keys and NOT NULL integrity constraints are common. This combination forces the user to enter values in the unique key and also eliminates the possibility that any new row's data will ever conflict with an existing row's data.
在数据库设计中，同一列上既定义唯一键约束又定义 NOT NULL 约束是很常见的。这种约束的组合能确保用户为唯一键输入值，同时还能确保用户输入的值不会与已有数据重复。
Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint. 提示：

在定义了复合 UNIQUE 键约束的键中，如果部分列为空值，则其余非空列的值不能重复，这是定义在多列上的 UNIQUE 键约束的搜索机制（search mechanism）规定的。 Each table in the database can have at most one PRIMARY KEY constraint. The values in the group of one or more columns subject to this constraint constitute the unique identifier of the row. In effect, each row is named by its primary key values.
每个数据库表上最多只能定义一个 PRIMARY KEY 约束（constraint）。构成此约束的列（一列或多列）的值可以作为一行数据的唯一标识符。即每个数据行可以由此主键值（primary key value）命名。
The Oracle implementation of the PRIMARY KEY integrity constraint guarantees that both of the following are true:
• No two rows of a table have duplicate values in the specified column or set of columns.
• The primary key columns do not allow nulls. That is, a value must exist for the primary key columns in each row.
Oracle 实现的 PRIMARY KEY 完整性约束（integrity constraint）能够确保表数据遵从以下两个规则：
• 任意两行数据的 PRIMARY KEY 约束列（一列或多列）不存在重复值。
• 主键列的值不为空。即用户必须为主键列输入值。
The columns included in the definition of a table's PRIMARY KEY integrity constraint are called the primary key. Although it is not required, every table should have a primary key so that:
• Each row in the table can be uniquely identified
• No duplicate rows exist in the table
包含于 PRIMARY KEY 完整性约束（integrity constraint）定义内的列被称为主键（primary key）。Oracle 不强制用户为表定义主键，但使用主键有以下好处：
• 表内的每行数据可以被唯一确定
• 表内不存在重复的数据行
Figure 21-5 illustrates a PRIMARY KEY constraint in the dept table and examples of rows that violate the constraint.
图 21-5 显示了定义在 dept 表上的 PRIMARY KEY 约束，以及违反此约束的数据行。
Figure 21-5 shows Table DEPT, which has three columns: DEPTNO, DNAME, and LOC. The DEPTNO column has a Primary Key--no row may duplicate a value in the key and no null values are allowed. This illustration shows that two rows cannot be inserted into Table DEPT because one duplicates an existing value in the primary key, while the other contains a null value for the primary 图 21-5 显示了 DEPT 表，表内包含 3 列：DEPTNO，DNAME，及 LOC。在 DEPTNO 列上定义了主键约束--此列不能有重复数据，且不能为空。图中还显示了两行因为违反主键约束而无法插入 DEPT 表的数据，一行数据的主键值与已有数据重复，另一行数据的主键列为空值。 Oracle enforces all PRIMARY KEY constraints using indexes. In Figure 21-5 , the primary key constraint created for the deptno column is enforced by the implicit creation of:
• A unique index on that column
• A NOT NULL constraint for that column
Oracle 使用索引来强制实现 PRIMARY KEY 约束。在 图 21-5 中， deptno 列上定义了主键约束，Oracle 的实现方式是隐式地创建索引：
• 在此列上创建唯一索引（unique index）
• 在此列上定义 NOT NULL constraint（约束）
Composite primary key constraints are limited to 32 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.
复合主键约束（composite primary key constraint）与复合索引（composite index）一样都受以下限制：复合索引键最多由 32 列构成。 隐式创建的索引名称与主键约束名称相同。用户使用 CREATE TABLE 或 ALTER TABLE 语句创建约束时，可以在 ENABLE 子句中为索引设定存储选项（storage option）。如果创建主键约束时已存在可用的唯一索引，主键约束将使用此索引而非隐式地创建新索引。
Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved.
在关系型数据库中，不同的表可以依据其共同的列产生关联关系，数据库需要确保数据遵从列关系的规则。引用完整性规则（referential integrity rule）就是用于确保列关系的规则。
The table that includes the foreign key. Therefore, it is the table that is dependent on the values present in the referenced unique or primary key.
Referenced or parent table
The table that is referenced by the child table's foreign key. It is this table's referenced key that determines whether specific inserts or updates are allowed in the child table.
A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
引用完整性约束要求子表每行数据的外键值都与一个父键（parent key）值相匹配。
Figure 21-6 shows a foreign key defined on the deptno column of the emp table. It guarantees that every value in this column must match a value in the primary key of the dept table (also the deptno column). Therefore, no erroneous department numbers can exist in the deptno column of the emp table.
图 21-6 显示了定义在 emp 表 deptno 列上的一个外键约束。此约束确保所有此列值均能与 dept 表（同样是 deptno 列）的一个主键值相匹配。因此 emp 表的 deptno 列中不会出现错误的部门编号。
Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.
外键约束可以定义于多列上。但复合外键（composite foreign key）必须引用列数及列数据类型均相同的复合主键（composite primary key）或复合唯一键（composite unique key）。由于复合主键及复合唯一键的列数不能超过 32 个，因此复合外键也受此限制。
Another type of referential integrity constraint, shown in Figure 21-7 , is called a self-referential integrity constraint. This type of foreign key references a parent key in the same table.
图 21-7 显示了另一类引用完整性约束--自引用完整性约束（self-referential integrity constraint）。在这类约束中，外键（foreign key）与其引用的父键（parent key）在同一表中。
In Figure 21-7 , the referential integrity constraint ensures that every value in the mgr column of the emp table corresponds to a value that currently exists in the empno column of the same table, but not necessarily in the same row, because every manager must also be an employee. This integrity constraint eliminates the possibility of erroneous employee numbers in the mgr column.
图 21-7 中，引用完整性约束确保 emp mgr 列的每个值都能与同表 empno 列（不需要在同一行）中的一个值相匹配，因为每个经理也是一个员工。此约束保证了 mgr 列中不会出现错误的员工编号。
The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.
关系型模型允许外键（foreign key）与其引用的主键（primary key）或唯一键（unique key）相匹配，或为空值（null）。如果一个复合外键的部分列为空，则此外键的非空列值无需与父键（parent key）中对应的列值相匹配。
Referential integrity constraints can specify particular actions to be performed on the dependent rows in a child table if a referenced parent key value is modified. The referential actions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE NO ACTION , and DELETE CASCADE .
在引用完整性约束（referential integrity constraint）定义中，可以设定当被引用的父键（parent key）值被修改后，对子表（child table）中的依赖数据该执行何种操作。Oracle FOREIGN KEY 完整性约束定义中支持的 引用操作 （referential action）有 UPDATE ， DELETE NO ACTION ，及 DELETE CASCADE 。
Other referential actions not supported by FOREIGN KEY integrity constraints of Oracle can be enforced using database triggers.
See Chapter 22, " Triggers " for more information. 提示：

Oracle FOREIGN KEY 完整性约束定义中不支持的引用操作可以通过数据库触发器实现。

见第 22 章，“ 触发器 ”了解更多信息。 The No Action (default) option specifies that referenced key values cannot be updated or deleted if the resulting data would violate a referential integrity constraint. For example, if a primary key value is referenced by a value in the foreign key, then the referenced primary key value cannot be deleted because of the dependent data.
禁止操作 （No Action）选项（默认操作）的含义是：如果对引用键值（referenced key value）的更新或删除操作将破坏引用完整性约束，则此操作不能执行。例如，当一个主键值被一个外键值引用时，这个主键值拥有依赖数据，所以不能被删除。
A delete cascades when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to also be deleted. For example, if a row in a parent table is deleted, and this row's primary key value is referenced by one or more foreign key values in a child table, then the rows in the child table that reference the primary key value are also deleted from the child table.
串联删除 （delete cascade）选项的含义是：如果包含引用键值（referenced key value）的数据行被删除，则子表（child table）中所有包含依赖外键值（dependent foreign key value）得数据行也将被删除。例如，当父表（parent table）的一行数据被删除时，如果此行的主键值被子表中一个或多个外键值引用，则子表中引用了此主键值的所有数据行也将被删除。
A delete sets null when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to set those values to null. For example, if employee_id references manager_id in the TMP table, then deleting a manager causes the rows for all employees working for that manager to have their manager_id value set to null.
置空 （set null）选项的含义是：如果包含引用键值（referenced key value）的数据行被删除，则子表（child table）中所有依赖外键值（dependent foreign key value）将被置空。例如，在 TMP employee_id 列引用了 manager_id 列，删除一条经理数据时，所有为此经理工作的员工的 manager_id 值将被置空。
Table 21-1 outlines the DML statements allowed by the different referential actions on the primary/unique key values in the parent table, and the foreign key values in the child table.
表 21-1 总结了采用不同引用操作（referential action）选项时，对父表（parent table）的主键值（primary key value）/唯一键值（unique key value）及子表（child table）的外键值（foreign key value）可以执行的 DML 语句。
Allowed if the statement does not leave any rows in the child table without a referenced parent key value.
Allowed if the new foreign key value still references a referenced key value.
You almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.
在大多数情况下，用户需要在外键（foreign key）上创建索引。但有一种情况例外，当外键所引用的主键（primary key）或唯一键（unique key）从不更新或删除时，外键上可以不创建索引。
Oracle maximizes the concurrency control of parent keys in relation to dependent foreign key values. You can control what concurrency mechanisms are used to maintain these relationships, and, depending on the situation, this can be highly beneficial. The following sections explain the possible situations and give recommendations for each.
Oracle 负责进行并发控制（concurrency control），确保数据访问时父键（parent key）及依赖外键（dependent foreign key）之间关系的正确性。用户可以根据实际情况选择并发控制的机制。以下各节将介绍不同情况，及在各种情况下 Oracle 建议的并发控制机制。
Figure 21-8 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.
如果外键（foreign key）上没有定义索引，当父表（parent table）中的数据行被更新或删除时，Oracle 采用的锁机制（locking mechanism）如 图 21-8 所示。而向父表中插入数据时无需对子表（child table）加锁。
Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table lock , SSX ) on the foreign key table. This prevents DML on the table by other transactions. The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.
如果外键上没有定义索引，对父表的主键执行 DML 操作时必须获得子表上的共享行排他表级锁（share row exclusive table lock）（也称为 share-subexclusive table lock，SSX ）。此锁能够阻止其他事务对子表执行 DML 操作。SSX 锁在获得后立即释放。如果父表中有多个主键值被更新或删除，对每行执行 DML 操作时都需要对子表进行一次加锁及解锁操作。
Indexed foreign keys only cause a row share table lock (also sometimes called a subshare table lock , SS ). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table. 另见：

如果外键上定义了索引，则对父表的主键执行 DML 操作时只需获得子表上的行共享表级锁（row share table lock）（也称为 subshare table lock ， SS ）。此锁不允许其他事务排他地对子表加锁，但允许对父表及子表执行 DML 操作。 Figure 21-8 shows a parent table and its child table. Rows 1 through 4 of the parent table are indexed on keys 1 through 4, respectively. The child table is not foreign-key indexed to the parent table. Row 3 in the parent table is updated and acquires an exclusive row lock. At the same time, the child table acquires a share lock on the whole table. 图 21-8 显示了一个父表及一个相关的子表。父表的键上定义了索引。子表的外键上没有定义索引。当父表的第 3 行被更新时，事务获得了父表上的排他行级锁，同时获得了子表上的共享表级锁。 Figure 21-9 illustrates the locking mechanisms used when an index is defined on the foreign key, and new rows are inserted, updated, or deleted in the child table.
如果外键（foreign key）上定义了索引，当插入，更新，或删除子表（child table）中的数据行时采用的锁机制如 图 21-9 所示。
Indexed foreign keys cause a row share table lock (also sometimes called a subshare table lock , SS ). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table.
如果外键上定义了索引，事务需要获得父表（parent table）及子表上的行共享表级锁（row share table lock）（也被称为 subshare table lock ， SS ）。此锁能够阻止其他事务排他地对表加锁，但允许对表执行 DML 操作。
This situation is preferable if there is any update or delete activity on the parent table while update activity is taking place on the child table. Inserts, updates, and deletes on the parent table do not require any locks on the child table, although updates and deletes will wait for row-level locks on the indexes of the child table to clear.
如果对子表进行更新操作的同时有可能存在对父表的更新或删除操作，则适宜采取本节讲述的锁机制。对父表的插入，更新，及删除操作无需获得子表上的锁，但更新及删除操作需要等待子表索引上的行级锁。
Figure 21-9 shows a parent table and its child table. Rows 1 through 4 of the parent table are indexed on keys 1 through 4, respectively. Row 5 in the child table is updated and acquires an exclusive row lock (key 2). 图 21-9 显示了一个父表及一个相关的子表。父表及子表的键上均定义了索引。当子表的第 5 行被更新时，事务获得了索引上的排他行级锁。 If the child table specifies ON DELETE CASCADE , then deletes from the parent table can result in deletes from the child table. In this case, waiting and locking rules are the same as if you deleted yourself from the child table after performing the delete from the parent table.
如果子表的外键约束定义中指定了 ON DELETE CASCADE 选项，则删除主表数据时将导致相关的子表数据同时被删除。在这种情况下，Oracle 采用的锁机制与用户先手工地删除主表数据，再删除相关的子表数据时采用的锁机制相同。
A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table. If a DML statement results in the condition of the CHECK constraint evaluating to false, then the statement is rolled back.
CHECK 完整性约束（integrity constraint）定义于列或列集上，此约束要求数据行满足用户定义的检查条件（或条件判断结果不确定（unknown））。如果一个 DML 语句使 CHECK 完整性约束的检查结果为假（false），则此语句将被回滚（rolled back）。
CHECK constraints let you enforce very specific integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitations:
• It must be a Boolean expression evaluated using the values in the row being inserted or updated, and
• It cannot contain subqueries; sequences; the SQL functions SYSDATE , UID , USER , or USERENV ; or the pseudocolumns LEVEL or ROWNUM .
用户可以使用 CHECK 约束定义检查条件（check condition）来实现特殊的完整性规则（integrity rule）。定义 CHECK 约束的检查条件时有以下限制：
• 检查条件必须是用于评估被插入或被更新数据行内的值的布尔表达式（Boolean expression）。
• 检查条件中不能包含：子查询（subquery）；序列（sequence）； SYSDATE ， UID ， USER ，及 USERENV 等 SQL 函数； LEVEL 或 ROWNUM 虚列（pseudocolumn）。
In evaluating CHECK constraints that contain string literals or SQL functions with globalization support parameters as arguments (such as TO_CHAR , TO_DATE , and TO_NUMBER ), Oracle uses the database globalization support settings by default. You can override the defaults by specifying globalization support parameters explicitly in such functions within the CHECK constraint definition.
如果 CHECK 约束的检查条件中包含 预定义字符串 （string literal）或使用全球化支持参数（globalization support parameter）的 SQL 函数（例如 TO_CHAR ， TO_DATE ，及 TO_NUMBER ），Oracle 默认使用数据库的全球化支持设置。用户也可以在定义 CHECK 约束的检查条件时，显式地设定全球化支持参数。
A single column can have multiple CHECK constraints that reference the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column.
同一列可以被多个 CHECK 约束（constraint）的条件定义所引用。用户为某一列定义的 CHECK 约束的数量不受限制。
If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions. Oracle does not verify that CHECK conditions are not mutually exclusive.
如果用户为在一列上创建了多个 CHECK 约束，必须确保各个约束的检查条件不会相互冲突。 CHECK 约束的检查顺序是不确定的。Oracle 也不会检查各个 CHECK 约束是否为互斥的（mutually exclusive）。
To know what types of actions are permitted when constraints are present, it is useful to understand when Oracle actually performs the checking of constraints. Assume the following:
• The emp table has been defined as in Figure 21-7 .
• The self-referential constraint makes the entries in the mgr column dependent on the values of the empno column. For simplicity, the rest of this discussion addresses only the empno and mgr columns of the emp table.
用户应该理解 Oracle 何时执行约束检查（checking of constraint），这有助于明确存在各种约束时允许执行的操作类型。现在通过以下示例说明：
图 21-7 所示的 emp
• 在 emp 表上定义自引用约束（self-referential constraint）， mgr 列的值依赖于 empno 列的值。为了简化示例，以下内容只针对 emp 表的 empno 及 mgr 列展开讨论。
Consider the insertion of the first row into the emp table. No rows currently exist, so how can a row be entered if the value in the mgr column cannot reference any existing value in the empno column? Three possibilities for doing this are:
• A null can be entered for the mgr column of the first row, assuming that the mgr column does not have a NOT NULL constraint defined on it. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.
• The same value can be entered in both the empno and mgr columns. This case reveals that Oracle performs its constraint checking after the statement has been completely run. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first run the statement (that is, insert the new row) and then check to see if any row in the table has an empno that corresponds to the new row's mgr .
• A multiple row INSERT statement, such as an INSERT statement with nested SELECT statement, can insert rows that reference one another. For example, the first row might have empno as 200 and mgr as 300, while the second row might have empno as 300 and mgr as 200.
  This case also shows that constraint checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations. You can also defer the checking of constraints until the end of the transaction .
emp 表插入第一条数据。由于此时表内没有数据， mgr 列无法引用 empno 列已有的值，数据该如何插入？在这种情况下有以下三种可能：
• 如果 mgr 列上没有定义 NOT NULL 约束，可以在第一行的 mgr 列上输入一个空值。由于外键约束允许空值，所以此行能成功插入表中。
• 可以向第一行的 empno 及 mgr 列输入一个相同的值。此种情况说明 Oracle 是在语句运行完成后执行的约束检查（constraint checking）。如果在第一行的父键（parent key）及外键（foreign key）插入相同的值，Oracle 必须首先运行语句（即插入数据行），再检查此行数据的 mgr 列值是否能与此表内的某个 empno 列值相匹配。
• 执行一个多行的 INSERT 语句，例如与 SELECT 语句结合的 INSERT 语句，将插入存在相互引用关系的多行数据。例如，第一行的 empno 列值为 200， mgr 列值为 300，而第二行的 empno 列值为 300， mgr 列值为 200。
  此种情况也说明 Oracle 将约束检查延迟（defer）直至语句运行结束。所有数据行首先被插入，之后 Oracle 逐行检查是否存在违反约束的情况。用户也可设置将约束检查延迟至 事务 （transaction）结束后。
Consider the same self-referential integrity constraint in this scenario. The company has been sold. Because of this sale, all employee numbers must be updated to be the current value plus 5000 to coordinate with the new company's employee numbers. Because manager numbers are really employee numbers, these values must also increase by 5000 (see Figure 21-10 ).
现在用上述的自引用约束再举一个例子。假设公司被收购，所有员工编号需要被更新为当前值加 5000，以便和新公司的员工编号保持一致。由于经理编号也是员工编号，所以此值也需要加 5000（见 图 21-10 ）。
Figure 21-10 shows the emp table with 2 columns: empno and mgr. The empno column has 3 rows: 210, 211, and 212. The mgr column has 2 rows: 210 and 211. 图 21-10 显示了 emp 表，其中包含两列：empno 与 mgr。empno 列有 3 个值：210，211，及 212。mgr 列有两个值：210 和 211。 Even though a constraint is defined to verify that each mgr value matches an empno value, this statement is legal because Oracle effectively performs its constraint checking after the statement completes. Figure 21-11 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked.
尽管 emp 表上定义的约束要求每个 mgr 值必须能和一个 empno 值相匹配，此语句仍旧可以执行，因为 Oracle 在语句执行后才进行约束检查。 图 21-11 表明 Oracle 执行了 SQL 语句的全部操作，之后才进行约束检查。
Figure 21-11 shows the emp table with 2 columns: empno and mgr. The empno column has 3 rows: 210, 211, and 212. The mgr column has 2 rows: 210 and 211.
5000 is added to each employee number, and therefore each manager. In the first box, under the empno column, 210 is updated to 5210. In the second box, under the empno column, 211 is updated to 5211 and under the mgr column, 210 is updated to 5210. In the third box, under the empno column, 212 is updated to 5212 and under the mgr column, 211 is updated to 5211. Then, constraints are checked. 图 21-10 显示了 emp 表，其中包含两列：empno 与 mgr。empno 列有 3 个值：210，211，及 212。mgr 列有两个值：210 和 211。
首先为每个员工编号加 5000，再为每个经理编号加 5000。在第一步中，empno 列的 210 被更新为 5210。在第二步中，empno 列的 211 被更新为 5211，mgr 列的 210 被更新为 5210。在第三步中，empno 列的 212 被更新为 5212，mgr 列的 211 被更新为 5211。最后 Oracle 执行约束检查。 The examples in this section illustrate the constraint checking mechanism during INSERT and UPDATE statements. The same mechanism is used for all types of DML statements, including UPDATE , INSERT , and DELETE statements.
本节的示例说明了 INSERT 及 UPDATE 语句的约束检查机制。事实上各类 DML 语句的约束检查机制均相同，这些 DML 语句包括 UPDATE ， INSERT ，及 DELETE 语句。
The examples also used self-referential integrity constraints to illustrate the checking mechanism. The same mechanism is used for all types of constraints, including the following:
• NOT NULL
• UNIQUE
• PRIMARY KEY
• All types of FOREIGN KEY constraints
• CHECK constraints
本节的示例说明了自引用约束的约束检查机制。事实上各类约束均采用相同的约束检查机制，这些约束包括：
• NOT NULL
• UNIQUE
• PRIMARY KEY
• 所有类型的 FOREIGN KEY 约束
• CHECK Default values are included as part of an INSERT statement before the statement is parsed. Therefore, default column values are subject to all integrity constraint checking.
  Oracle 首先为用户提交的 INSERT 语句赋予默认列值（default column value），之后再进行解析（parse）。因此默认列值也需要经过所有完整性约束检查（integrity constraint checking）。
  You can defer checking constraints for validity until the end of the transaction.
  • A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to undo.
  • If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.
  用户可以将约束检查（checking constraint for validity） 延迟 （defer）至事务结束时进行。
  • 如果 Oracle 在事务提交（commit）时才对约束执行检查，则称此约束是 延迟的 （deferred）。如果数据违反了延迟约束，提交操作将导致事务被回滚（undo）。
  • 如果约束是 即时的 （immediate）（非延迟的），则此约束将在语句执行结束后进行检查。如果数据违反了延迟约束，语句将被立即回滚。
  If a constraint causes an action (for example, delete cascade), that action is always taken as part of the statement that caused it, whether the constraint is deferred or immediate.
  如果在约束中定义了 操作 （action）（例如，串联删除（delete cascade）），这些操作将被视为导致此操作的语句的一部分，无论约束是延迟的或即时的。
  You can define constraints as either deferrable or not deferrable , and either initially deferred or initially immediate . These attributes can be different for each constraint. You specify them with keywords in the CONSTRAINT clause:
  • DEFERRABLE or NOT DEFERRABLE
  • INITIALLY DEFERRED or INITIALLY IMMEDIATE
  用户可以将约束定义为 可延迟的 （deferrable）或 不可延迟的 （not deferrable），以及 初始为延迟的 （initially deferred）或 初始为即时的 （initially immediate）。上述属性应与不同类型的约束结合使用。用户可以在 CONSTRAINT 子句中使用以下关键字进行设定：
  • DEFERRABLE 或 NOT DEFERRABLE
  • INITIALLY DEFERRED 或 INITIALLY IMMEDIATE
  Constraints can be added, dropped, enabled, disabled, or validated. You can also modify a constraint's attributes.
  约束可以被添加（add），移除（drop），启用（enable），禁用（disable），或验证（validate）。用户还可以修改约束的各种属性。
  Oracle Database SQL Reference for information about constraint attributes and their default values
  • " Constraint States "
  • " Constraint State Modification "
  The SET CONSTRAINTS statement makes constraints either DEFERRED or IMMEDIATE for a particular transaction (following the ANSI SQL92 standards in both syntax and semantics). You can use this statement to set the mode for a list of constraint names or for ALL constraints.
  SET CONSTRAINTS 语句的作用是设定指定事务内约束为 DEFERRED （延迟的）或 IMMEDIATE （即时的）（此语句在语法及语义上均符合 ANSI SQL92 标准）。用户可以使用此语句为一组约束设定模式，也可以使用 ALL 关键字对所有约束统一设定。
  The SET CONSTRAINTS mode lasts for the duration of the transaction or until another SET CONSTRAINTS statement resets the mode.
  SET CONSTRAINTS 语句的设定结果在整个事务内有效，除非用户再次使用 SET CONSTRAINTS 语句重设约束检查模式。
  SET CONSTRAINTS ... IMMEDIATE causes the specified constraints to be checked immediately on execution of each constrained statement. Oracle first checks any constraints that were deferred earlier in the transaction and then continues immediately checking constraints of any further statements in that transaction, as long as all the checked constraints are consistent and no other SET CONSTRAINTS statement is issued. If any constraint fails the check, an error is signaled. At that point, a COMMIT causes the whole transaction to undo.
  SET CONSTRAINTS ... IMMEDIATE 语句的作用是使其中指定的约束在被约束语句执行结束后即进行检查。执行此语句后，Oracle 首先检查事务内之前被延迟的约束，之后将对事务内的新语句进行即时约束检查，直到出现违反约束的情况，或用户提交了新的 SET CONSTRAINTS 语句。如果出现违反约束的情况，Oracle 将返回错误消息。 此时，Oracle 使用 COMMIT 语句使事务得到回滚。
  The ALTER SESSION statement also has clauses to SET CONSTRAINTS IMMEDIATE or DEFERRED . These clauses imply setting deferrable constraints (that is, you cannot specify a list of constraint names). They are equivalent to making a SET CONSTRAINTS statement at the start of each transaction in the current session.
  在 ALTER SESSION 语句内也可以使用 SET CONSTRAINTS IMMEDIATE DEFERRED 子句。此子句针对所有（ ALL ）可延迟的约束（deferrable constraint）（此子句无法选择约束名）。此语句的效果与在会话的每个事务开始时使用 SET CONSTRAINTS 语句的效果一致。
  Making constraints immediate at the end of a transaction is a way of checking whether COMMIT can succeed. You can avoid unexpected rollbacks by setting constraints to IMMEDIATE as the last statement in a transaction. If any constraint fails the check, you can then correct the error before committing the transaction.
  用户可以在提交事务前使用 SET CONSTRAINTS 语句将约束检查模式设为 即时的 （immediate），从而检查 COMMIT 语句是否能够成功。这能够避免非预期的事务回滚。如果存在检查失败的约束，用户可以在提交事务前修正错误。
  SET CONSTRAINTS can be a distributed statement. Existing database links that have transactions in process are told when a SET CONSTRAINTS ALL statement occurs, and new links learn that it occurred as soon as they start a transaction.
  SET CONSTRAINTS 语句可用于分布式语句（distributed statement）。当用户执行 SET CONSTRAINTS ALL 语句时，已经有事务在执行的数据库链接（database link）将立即得到通知，而新链接在开始事务时将得到通知。
  A user sees inconsistent constraints, including duplicates in unique indexes, when that user's transaction produces these inconsistencies. You can place deferred unique and foreign key constraints on materialized views, allowing fast and complete refresh to complete successfully.
  当用户事务造成数据存在不一致性（inconsistency）时，此用户将能查询这些不一致的数据，包括唯一索引中的重复数据。用户可以将物化视图（materialized view）上的唯一键约束（unique constraint）或外键约束（foreign key constraint）设置为延迟的（deferred），确保物化视图能够进行快速（fast）或完全（complete）更新。
  Deferrable unique constraints always use nonunique indexes. When you remove a deferrable constraint, its index remains. This is convenient because the storage information remains available after you disable a constraint. Not-deferrable unique constraints and primary keys also use a nonunique index if the nonunique index is placed on the key columns before the constraint is enforced.
  可延迟的唯一约束（deferrable unique constraint）总是使用非唯一索引（nonunique index）。 当用户移除一个可延迟的约束时，此约束使用的索引仍将被保留。因此当用户禁用（disable）一个约束后，此约束的存储信息依旧可用。如果在创建不可延迟的唯一约束或主键约束时，键列上已经创建了非唯一索引（nonunique index），上述约束将使用此非唯一索引。
  ensures that all incoming data conforms to the constraint
  • DISABLE allows incoming data, regardless of whether it conforms to the constraint
  • VALIDATE ensures that existing data conforms to the constraint
  • NOVALIDATE means that some existing data may not conform to the constraint
  • ENABLE （启用）确保所有输入的数据都遵从约束（constraint）
  • DISABLE （禁用）总是允许输入数据，无论数据是否遵从约束
  • VALIDATE （验证）确保已存在的数据遵从约束
  • NOVALIDATE （无验证）允许已存在的数据不遵从约束
  • ENABLE VALIDATE is the same as ENABLE . The constraint is checked and is guaranteed to hold for all rows.
  • ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.
    In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.
  • DISABLE NOVALIDATE is the same as DISABLE . The constraint is not checked and is not necessarily true.
  • DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.
    For a UNIQUE constraint, the DISABLE VALIDATE state enables you to load data efficiently from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION clause of the ALTER TABLE statement.
  • 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 来提高数据加载的效率。
  Transitions between these states are governed by the following rules:
  • ENABLE implies VALIDATE , unless NOVALIDATE is specified.
  • DISABLE implies NOVALIDATE , unless VALIDATE is specified.
  • VALIDATE and NOVALIDATE do not have any default implications for the ENABLE and DISABLE states.
  • When a unique or primary key moves from the DISABLE state to the ENABLE state, if there is no existing index, a unique index is automatically created. Similarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped.
  • When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.
  • Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDAT E state does not block reads, writes, or other DDL statements. It can be done in parallel.
  在上述状态间进行转换时存在以下规则：
  • 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 语句。 转换操作可以被并行执行。
  You can use the MODIFY CONSTRAINT clause of the ALTER TABLE statement to change the following constraint states:
  • DEFERRABLE or NOT DEFERRABLE
  • INITIALLY DEFERRED or INITIALLY IMMEDIATE
  • RELY or NORELY
  • USING INDEX
  • ENABLE or DISABLE
  • VALIDATE or NOVALIDATE
  • EXCEPTIONS INTO 用户可以使用 ALTER TABLE 语句的 MODIFY CONSTRAINT 子句修改约束的以下状态：
    • DEFERRABLE 或 NOT DEFERRABLE
    • INITIALLY DEFERRED 或 INITIALLY IMMEDIATE
    • RELY 或 NORELY
    • USING INDEX
    • ENABLE 或 DISABLE
    • VALIDATE 或 NOVALIDATE
    • EXCEPTIONS INTO [078] In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.
      [129] If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.
      [137] Indexed foreign keys cause a row share table lock (also sometimes called a subshare table lock , SS ). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table.
      [179] At that point, a COMMIT causes the whole transaction to undo.
      [190] It can be done in parallel. 1、只有这 3 种？[019] 提到的其他操作呢？
      [025] Oracle supports the use of FOREIGN KEY integrity constraints to define the referential integrity actions, including:
      2、具体过程？
      [049] When the data load is complete, you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
      3、 ENABLE 子句？
      [092] Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint.
      4、update 后能全部或部分为 null 么？
      [123] Allowed if the new foreign key value still references a referenced key value.
      5、怎么回事？应该什么时候释放？
      [129] The SSX lock is released immediately after it is obtained.
      6、整个 21.3.4.4 不理解
      [124] Concurrency Control, Indexes, and Foreign Keys
      7、此处说的 database link 指什么？
      [183] SET CONSTRAINTS can be a distributed statement. Existing database links that have transactions in process are told when a SET CONSTRAINTS ALL statement occurs, and new links learn that it occurred as soon as they start a transaction.
      8、21.5.3 不知所云？
      [185-186] A user sees inconsistent constraints, including duplicates in unique indexes, when that user's transaction produces these inconsistencies.