21 Data Integrity

001 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）的要求，并防止数据表中出现无效数据。
　
002 This chapter contains the following topics: 本章包含以下主题：
003

Introduction to Data Integrity

21.1 数据完整性简介
004 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 两个表，同时描述了这两个表存储信息时应遵从的业务规则。
　
005 Figure 21-1 Examples of Data Integrity
　		 图 21-1 数据完整性示例
　
006


　


　
007
Figure 21-1 shows tables DEPT and EMP. Table DEPT has three columns:
  • DEPTNO
  • DNAME
  • LOC

Each value in the DNAME column must be unique.

Table EMP has six columns:

  • EMPNO
  • ENAME
  • ... Other Columns ...
  • SAL
  • COMM
  • 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.
图 21-1 定义了 DEPT 及 EMP 两个表。DEPT 表包含 3 列：
  • DEPTNO
  • DNAME
  • LOC

DNAME 列的值必须唯一。

EMP 表包含 6 列：

  • EMPNO
  • ENAME
  • ... Other Columns ...
  • SAL
  • COMM
  • DEPTNO
每行的 ENAME 列必须有值。每行的 EMPNO 列必须有值，且值必须唯一。DEPTNO 列的值必须能与 DEPT 表的 DEPTNO 列的某个值相匹配。SAL 列的值不能超过 10,000。
008 Note that some columns in each table have specific rules that constrain the data contained within them.
　		 从图中可以看出，两个表的某些列上存在一定的规则，对存储于其中的数据起到了约束作用。
　
009

Types of Data Integrity

21.1.1 数据完整性的类型
010 This section describes the rules that can be applied to table columns to enforce different types of data integrity.
　		 本节讲述可以应用于数据表列上的规则，以及规则所实现的数据完整性（data integrity）的类型。
　
011

Null Rule

21.1.1.1 空规则
012 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），即没有值。
　
013

Unique Column Values

21.1.1.2 唯一列值
014 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）是定义在某一列（或某一列集）上的规则，其作用是确保将要被插入或更新的数据行此列（或列集）的值是唯一的。
　
015

Primary Key Values

21.1.1.3 主键值
016 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）（键指一列或一个列集）上的规则，其作用是确保表内的每一数据行都可以由某一个键值唯一地确定。
　
017

Referential Integrity Rules

21.1.1.4 引用完整性规则
018 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））相匹配。
　
019 Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:
  • 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 的默认操作为无操作。）
020

Complex Integrity Checking

21.1.1.5 复杂完整性检查
021 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）是一种用户定义的规则，针对某一列（或某一列集），其作用是依据数据行的列值来允许或禁止插入，更新，或删除此数据行。
　
022

How Oracle Enforces Data Integrity

21.1.2 Oracle 如何强制实现数据完整性
023 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）来定义。
　
024

Integrity Constraints Description

21.1.2.1 完整性约束描述
025 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）
026
Note:

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）规则。此种情况下可以使用数据库触发器（详见下节）。
027

Database Triggers

21.1.2.2 数据库触发器
028 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）的方式。
　
029
See Also:

Chapter 22, "Triggers" for examples of triggers used to enforce data integrity
另见：

第 22 章，“触发器” 了解如何使用触发器强制实现数据完整性
030

Overview of Integrity Constraints

21.2 完整性约束概述
031 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 将回滚语句并返回错误消息。
　
032
Note:

Operations on views (and synonyms for tables) are subject to the integrity constraints defined on the underlying base tables.
提示：

对视图（及表的同义词（synonym））执行的操作需要遵从底层基表（base tables）上的完整性约束。
033 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 将回滚语句并返回错误消息。
　
034 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 标准。
　
035

Advantages of Integrity Constraints

21.2.1 完整性约束的优势
036 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）存储过程强制实现业务规则
037
See Also:

Chapter 22, "Triggers"
另见：

第 22 章，“触发器”
038

Declarative Ease

21.2.1.1 声明即用
039 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）的灵活性。
　
040

Centralized Rules

21.2.1.2 规则集中化
041 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）具备此优势，但实现的复杂程度远大于声明完整性约束。
　
042

Maximum Application Development Productivity

21.2.1.3 应用程序开发效率最大化
043 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），相关的应用程序就能够自动地遵从修改后的约束。相反，如果在各个数据库应用程序代码中实现强制实现业务规则，开发人员必须修改相关的应用程序源代码并重新编译，调试，测试被修改的应用程序。
　
044

Immediate User Feedback

21.2.1.4 即时向用户反馈
045 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 应用程序可以在用户录入数据时利用数据字典中存储的完整性约束定义信息检查是否存在违反约束的情况，这种检查可以在应用程序提交语句之前进行。

　
046

Superior Performance

21.2.1.5 性能优势
047 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）可以利用声明信息了解数据情况，从而提高整体查询性能。（将完整性规则从应用程序代码或数据库触发器中提出能够确保这些规则只在必要时进行检查。）
　
048

Flexibility for Data Loads and Identification of Integrity Violations

21.2.1.6 数据加载时的灵活性及对违反完整性的数据的识别
049 You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking. 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.
　		 用户可以临时地禁用完整性约束（integrity constraint），以消除加载大量数据时约束检查造成的开销（overhead）。当数据加载结束后，用户可以再启用完整性约束，Oracle 能够自动地将违反完整性约束的新数据行存储到一个独立的异常数据表（exceptions table）中。
　
050

The Performance Cost of Integrity Constraints

21.2.2 完整性约束对性能的影响
051 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 语句大体相当。
　
052

Types of Integrity Constraints

21.3 完整性约束的类型
053 You can use the following integrity constraints to impose restrictions on the input of column values: 用户可以使用以下完整性约束（integrity constraint）对输入的列值加以限制：
054

NOT NULL Integrity Constraints

21.3.1 NOT NULL 完整性约束
055 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 约束，要求用户为每个数据行的此列输入一个值。
　
056 Figure 21-2 illustrates a NOT NULL integrity constraint.
　		 图 21-2 举例说明了一个 NOT NULL 完整性约束。
　
057 Figure 21-2 NOT NULL Integrity Constraints
　		 图 21-2 NOT NULL 完整性约束
　
058


　


　
059
Figure 21-2 shows Table EMP, which has eight columns:
  • EMPNO
  • ENAME
  • JOB
  • MGR
  • HIREDATE
  • SAL
  • COMM
  • 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.
图 21-2 显示了 EMP 表，此表由 8 列构成：
  • EMPNO
  • ENAME
  • JOB
  • MGR
  • HIREDATE
  • SAL
  • COMM
  • DEPTNO
在 ENAME 列上定义了一个 NOT NULL 约束--任何数据行此列都不能为空值。COMM 列上没有 NOT NULL 约束--任何数据行此列都可以为空值。
060

UNIQUE Key Integrity Constraints

21.3.2 UNIQUE 键完整性约束
061 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）要求列或列集（即键）的值唯一，数据表任意两行某列或某个列集的值不重复。
　
062 For example, in Figure 21-3 a 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 键约束，不允许此表内存在重复的部门名称。
　
063 Figure 21-3 A UNIQUE Key Constraint
　		 图 21-3 UNIQUE 键约束
　
064


　


　
065
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 约束，则上述数据也不允许被插入。
066

Unique Keys

21.3.2.1 唯一键
067 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）定义中所引用的列或列集。
　
068 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 两列。
　
069 Figure 21-4 A Composite UNIQUE Key Constraint
　		 图 21-4 复合 UNIQUE 键约束
　
070


　


　
071
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 约束，则不允许输入空值。
072 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）的组合不能重复。这能避免因疏忽造成电话号码重复问题。
　
073

UNIQUE Key Constraints and Indexes

21.3.2.2 UNIQUE 键约束与索引
074 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 列构成。
　
075
Note:

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）的容量（以字节为单位）可以与一个数据块的容量相当。而在此前的数据库版本中，一个键值的容量不能超过数据块容量的一半。
076 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 索引，新建约束将使用此索引，而不再隐式地创建新索引。
　
077

Combine UNIQUE Key and NOT NULL Integrity Constraints

21.3.2.3 UNIQUE 键约束与 NOT NULL 约束结合使用
078 In Figure 21-3 and 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 键约束。
　
079 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 约束是很常见的。这种约束的组合能确保用户为唯一键输入值，同时还能确保用户输入的值不会与已有数据重复。
　
080
Note:

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）规定的。
081

PRIMARY KEY Integrity Constraints

21.3.3 PRIMARY KEY 完整性约束
082 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）命名。
　
083 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 约束列（一列或多列）不存在重复值。
  • 主键列的值不为空。即用户必须为主键列输入值。
084

Primary Keys

21.3.3.1 主键
085 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 不强制用户为表定义主键，但使用主键有以下好处：
  • 表内的每行数据可以被唯一确定
  • 表内不存在重复的数据行
086 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 约束，以及违反此约束的数据行。
　
087 Figure 21-5 A Primary Key Constraint
　		 图 21-5 主键约束
　
088


　


　
089
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 key.
图 21-5 显示了 DEPT 表，表内包含 3 列：DEPTNO，DNAME，及 LOC。在 DEPTNO 列上定义了主键约束--此列不能有重复数据，且不能为空。图中还显示了两行因为违反主键约束而无法插入 DEPT 表的数据，一行数据的主键值与已有数据重复，另一行数据的主键列为空值。
090

PRIMARY KEY Constraints and Indexes

21.3.3.2 PRIMARY KEY 约束与索引
091 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（约束）
092 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）。如果创建主键约束时已存在可用的唯一索引，主键约束将使用此索引而非隐式地创建新索引。
　
093

Referential Integrity Constraints

21.3.4 引用完整性约束
094 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）就是用于确保列关系的规则。
　
095 The following terms are associated with referential integrity constraints.
　		 以下是与引用完整性约束（referential integrity constraint）相关的术语。
　
096
Term Definition
Foreign key
　		 The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key.
　
Referenced key
　		 The unique key or primary key of the same or different table that is referenced by a foreign key.
　
Dependent or child table
　		 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.
　
　
术语 定义
外键（foreign key）
　		 引用完整性约束定义中包含的列（一列或多列），外键引用了引用键。
　
引用键（referenced key）
　		 被外键引用表（可以是不同的表或同一个表）的唯一键（unique key）或主键（primary key）。

　
依赖表（dependent table）或子表（child table）
　		 定义了外键约束的表。此表依赖于被引用的唯一键值或主键值。
　
引用表（referenced table）或父表（parent table）
　		 被子表外键引用的表。此表的引用键决定了能否向子表插入数据或更新其中的数据。
　
　
097 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）值相匹配。
　
098 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 列中不会出现错误的部门编号。
　
099 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 个，因此复合外键也受此限制。
　
100 Figure 21-6 Referential Integrity Constraints
　		 图 21-6 引用完整性约束
　
101


　


　
102
Figure 21-6 is described in the text preceding the image.
图 21-6 将在下文中说明。
103

Self-Referential Integrity Constraints

21.3.4.1 自引用完整性约束
104 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）在同一表中。
　
105 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 列中不会出现错误的员工编号。
　
106 Figure 21-7 Single Table Referential Constraints
　		 图 21-7 单表引用完整性约束
　
107


　


　
108
Figure 21-7 is described in the text preceding the image.
图 21-7 将在下文中说明。
109

Nulls and Foreign Keys

21.3.4.2 空值与外键
110 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）中对应的列值相匹配。
　
111

Actions Defined by Referential Integrity Constraints

21.3.4.3 在引用完整性中定义的操作
112 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。
　
113
Note:

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 章，“触发器”了解更多信息。
114

Delete No Action

21.3.4.3.1 禁止操作
115 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）的更新或删除操作将破坏引用完整性约束，则此操作不能执行。例如，当一个主键值被一个外键值引用时，这个主键值拥有依赖数据，所以不能被删除。
　
116

Delete Cascade

21.3.4.3.2 串联删除
117 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）的一行数据被删除时，如果此行的主键值被子表中一个或多个外键值引用，则子表中引用了此主键值的所有数据行也将被删除。
　
118

Delete Set Null

21.3.4.3.3 置空
119 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 值将被置空。
　
120

DML Restrictions with Respect to Referential Actions

21.3.4.3.4 各引用操作对 DML 的限制
121 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 语句。
　
122 Table 21-1 DML Statements Allowed by Update and Delete No Action
　		 表 21-1 各引用操作对 DML 的限制
　
123
DML Statement Issued Against Parent Table Issued Against Child Table
INSERT
　		 Always OK if the parent key value is unique.
　		 OK only if the foreign key value exists in the parent key or is partially or all null.
　
UPDATE No Action
　		 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.
　
DELETE No Action
　		 Allowed if no rows in the child table reference the parent key value.
　		 Always OK.
　
DELETE Cascade
　		 Always OK.
　		 Always OK.
　
DELETE Set Null
　		 Always OK.
　		 Always OK.
　
　
DML 语句
　		 对父表执行
　		 对子表执行
　
INSERT
　		 当父键值唯一时允许执行。
　		 当外键值能与一个父键值匹配，或外键值部分或全部为空时允许执行。
　
UPDATE No Action
　		 如果更新操作不会使子表内的任何数据行没有可引用的父键值，则允许执行。
　		 如果新外键值依旧能与一个父键值匹配，则允许执行。
　
DELETE No Action
　		 如果子表没有引用此主键值则允许执行。
　		 允许执行。
　
DELETE Cascade
　		 允许执行。
　		 允许执行。
　
DELETE Set Null
　		 允许执行。
　		 允许执行。
　
　
124

Concurrency Control, Indexes, and Foreign Keys

21.3.4.4 并发控制，索引，及外键
125 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）从不更新或删除时，外键上可以不创建索引。
　
126 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 建议的并发控制机制。
　
127

No Index on the Foreign Key

21.3.4.4.1 外键上无索引
128 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.