11G Concept 第五章翻译 Data Integrity(数据完整性)

本章解释了完整性约束是怎样强制实施数据库中的“业务规则”的，以及怎样预防无效的信息插入到表中。

本章包含下面几个小节：

·Introduction to Data Integrity（数据完整性介绍）

·Types of Integrity Constraints（完整性约束的种类）

·states of Integrity Constraints（完整性约束的状态）

 

Introduction to Data Integrity(数据完整性介绍)

“业务规则” 设置了一些条件和“关系”，这些条件要么必须符合，要么必须不符合

举个例子：

每个公司都有它自己的工资政策，雇员编号，库存跟踪，等等。

维护数据完整性，是指遵守DBA或者开发定义在数据库上的规则，这很重要。

 

Techniques for Guaranteeing DataIntegrity(保证数据完整性的技术)

当设计一个数据库应用，开发人员有很多选项来保证存入在数据库中数据的完整性。这些选项波扩：

·使用触发器来保证业务规则

·使用存储过程完完全全控制数据的访问

·在应用程序代码中保证业务规则

·使用Oracle数据库完整性约束，它是值定义在列or表级别的规则，用来约束数据。

这一张主要解释了完整性约束的基本原理

 

Advantages of Integrity Constraints（完整性约束的优势）

一个完整性约束是一个schema object，它创建和删除都使用SQL。为了强制保证数据完整性，尽可能使用完整性约束。

在保证数据完整性上，对比其他手段，完整性约束的优势包括：

·Declarative ease（声明简单）

  使用SQL来定义完整性约束，你在定义或者修改一个表定义时，不需要额外的编程工作

  SQL语句易于编写，也易于排除编程故障。

·Centralized rules（集中的规则）

  完整性约束在表上定义，然后存在数据字典。因此，任何应用输入的数据 都必须遵守相同的完整性约束。如果表级别的规则有了变化，那么应用是不需要变化的。同样，应用可以使用数据字典中的元数据立即提醒用户的违规操作，甚至在数据库检查SQL语句之前。

·Flexibility when loading data灵活性（当大量加载数据时）

  你可以临时将约束设为disable，用来避免加载大量数据时候的性能开销。当数据加载完成后，你可以再将约束设回enable。

 

Types of Integrity Constraints（完整性约束的种类）

Oracle数据库使你可以在 表级 和段级 两个级别应用约束。

约束可以指定为列或属性定义的一部分，称之为inline规范。

约束可以指定为表定义的一部分，称之为out-of-line规范

术语key在好几种完整性约束类型定义中都有使用。

某些完整性约束类型的定义中，包含了的列，或者一组列，称之为key。

key描述了关系型数据库中表和列的关系 ，key中每个值，称之为key values。

下表描述了约束的种类，除了NOT NULL（必须inline），其他都可以是inline或者是out-of-line。

Constraint Type	Description	See Also
NOT NULL	Allows or disallows inserts or updates of rows containing a null in a specified column.	"NOT NULL Integrity Constraints"
Unique key	Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.	"Unique Constraints"
Primary key	Combines a NOT NULL constraint and a unique constraint. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.	"Primary Key Constraints"
Foreign key	Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key.	"Foreign Key Constraints"
Check	Requires a database value to obey a specified condition.	"Check Constraints"
REF	Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values. In an object-relational database, a built-in data type called a REF encapsulates a reference to a row object of a specified object type. Referential integrity constraints on REF columns ensure that there is a row object for the REF.	Oracle Database Object-Relational Developer's Guide to learn about REF constraints

 

NOT NULL Integrity Constraints（非空完整性约束）

NOT NULL约束，要求表中的一列不允许有null值。不存在值就是NULL，默认情况下，表中的所有列都是允许有null的。

NOT NULL主要用于不能缺少值的列。举个例子，hr.employees表要求last_name列有值。企图插入一个雇员信息，但少了last_name列，将会产生如下错误：

SQL>INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');

.

.

ERROR at line1:

ORA-01400:cannot insert NULL into("HR"."EMPLOYEES"."LAST_NAME")

在表中有数据是，你是没办法直接添加一个具有not null约束的列，除非你有指定默认值。

 

Unique Constraints（唯一约束）

唯一键约束要求一个列的值，或者一组列组合的值必须是唯一的。在添加了唯一约束的表，没有行可以在唯一列或者唯一组合列上拥有完全相同的值。

  注意：这里的术语key仅仅只在完整性约束中指定的列。因为数据库创建唯一索引时，会强制隐式创建，或者重用一个在key上面的索引，术语unique key 有一些情况下会被错误的用做unique key constraint 或者unique index的同义词。

唯一键约束是为不允许有重复值的列准备的。唯一键约束和主键约束不一样，主键的目的是唯一标识中表中的每一行，通常它只要求唯一，而不要求列中的值有什么意义。

使用唯一键约束的场景如下：

·用户电话号码，其主键是用户编号

·部门名称，其主键是部门编号

 

像下面例子显示，唯一键约束在employees表的email列，相关部分如下：

CREATE TABLEemployees    ( ...

    , 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_IDLAST_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 line1:

ORA-00001:unique constraint (HR.EMP_EMAIL_UK) violated

 

除非NOT NULL约束也定义了，否则NULL永远满足唯一性约束（可以插入）。因此 典型的情况是列上有唯一约束和NOT NULL约束。这种组合强制用户输入的值是唯一的，消除了新数据和已经存在数据的冲突性。

  注意：根据唯一键约束在多列上的扫描机制，组合唯一键索引中，null和非null值的组合，非null值无法重复

 

Primary Key Constraints(主键约束)

在主键约束中，一个或多个列组合的值唯一的标识出行。每个表只能有一个主键，它的作用是命名一行，保证没有重复的行存在。

主键可以是自然值（natural）或者是专门的替代值（surrogate）。 自然键有意义的标识符，使用的是表中已经存在的列。举个例子，自然键可以是邮局表中的 邮政编码。比较起来，替代键是系统产生 自动增长的标识符，保证在这个表中 值是唯一的。

通常，替代键都是用序列生成的

 

Oracle数据库中主键约束保证如下：

·在指定的列 或 几列，不会出现重复的值

·主键列不允许出现null值

通常情况下雇员的雇员数字编号被称之为主键。每个雇员必须拥有唯一的ID号。一个雇员的情况在employees表中，必须用一行描述清楚（不能多行）。

下面例子中employee ID是主键，表中已经存在有一个ID是 202的员工

下面的案例则显示出当企图增加一个雇员 使用已经有人用了的202，或者增加主键列使用null值（不对这列进行输入值）：

SQL>INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)   

  1 VALUES (202,'Chan','ICHAN',SYSDATE,'ST_CLERK');

.

.

.

ERROR at line1:

ORA-00001:unique constraint (HR.EMP_EMP_ID_PK) violated

 

SQL>INSERT INTO employees (last_name) VALUES ('Chan');

.

.

.

ERROR at line1:

ORA-01400:cannot insert NULL into("HR"."EMPLOYEES"."EMPLOYEE_ID")

数据库强制主键约束要一个索引。通常，在一列上创建一个主键约束，那么会在这列隐式创建一个唯一索引 和一个NOT NULL约束。下面有一些标注了特殊情况：

·在一些情况下，你会创建主键约束为延迟约束（提交后才检查），这时创建的索引是非唯一索引。

    注意：你可以显示创建爱你唯一索引通过使用CREATE UNIQUE INDEX语句

·当主键创建时，一个可用的索引存在，那么约束会重用这个索引，不会再隐式的创建一个新的。

默认情况下，隐式创建的索引的名称，和约束的名称一样。你同样可以自定义索引的名称。你可以在包含了ENABLE子句的CREATE TABLE或 ALTER TABLE语句中创建约束，并指定索引的存储选项。

 

Foreign Key Constraints（外键约束）

任何时候，两个表包含了一个或者多个相同的列，Oracle数据库可以强制保证两个表之间的关心，通过使用外键约束，也叫做 引用完整性约束。

这个约束要求在 在约束指定列上的值，必须和 引用的其他表那个列的值相匹配。

引用完整性规则的一个例子是 employee的部门编号 必须是在部门表中存在的。

下面的表列出了引用完整性约束的相关术语：

Term	Definition
Foreign key	定义在约束中的列，就是它去参考 Referenced key。举个例子，employees表的department_id列就是一个Foreign key，它引用了departments表的department_id列。 Foreign keys可以定义在多列。不管怎样，composite foreign key（组合外键）必须引用 相同数量列的组合主键或者唯一键。并且要对应的数据类型。 foreign key的值可以和应用列中的值匹配，或为null。如果组合foreign key 任意一列是null，那么这一个组合不需要匹配任何Referenced key 也能存在（所以说，foreign key列最好设not null）
Referenced key	一个唯一键或者主键。被foreign key 引用。举个例子，departments表的department_id列就是Referenced key。被employees表的departmen_id列引用。
Dependent or child table	包含foreign key的表就是子表，从属表。举个例子，employees表就是departments的子表。
Referenced or parent table	包含referenced列的表，对于子表来说，就是父表。举个例子departments表就是employees表的父表。

 

下图显示了在employees.department_id列的foreign key。它保证了在这列的每个值，都必须与departments.department_id列的值存在的值匹配，因此，在employees.department_id列是不会出现错误的部门号的。

Self-Referential Integrity Constraints(自引用完整性约束)

下面的图展示了自引用完整性约束。在这种情况下，foreign key引用的refenrencedkey在自己这个表本身。

在下面的途中，引用完整性约束保证了employees.manager_id列中的每个值，在employees.employee_id列都有存在。举个例子，雇员102 的领导，必须在雇员表中存在。这个约束保证manager_id列不会存在错误数据。

Null and Foreign Keys（关于外键中的null值）

foreign keys要么和referencedkey进行匹配，要么是null值

举个例子，一个用户不指定department_id 然后插入一行进入hr.employees表

组合foreign key中任何一列是null，那么都可以随便插入。

 

Parent KeyModifications and Foreign Keys（父键（referenced key）的修改及对Foreign Keys的影响）

父键的删除会对 foreign keys 和父键之间的关心有影响

举个例子,如果用户企图删除一个部门。那么这个部门中的雇员 会发生什么？

·No action on deletion or update(对父表的更新和删除，子表没动作)

  普通情况下，当父键修改后的结果违犯 关联完整性约束时，父键是无法修改的

  举个例子 employees.department_id是个foreign key，关联到departments表。

  如果employees表中有某个部门的行，而现在要删除departments表中的这个部门。

  那就会违反这个约束，会报错。

·Cascading deletions（级联删除）

  级联删除指，一个包含了referenced key的行被删除，会导致子表中所有依赖的foreign key的相关行 会同时删除

  举个例子：删除了departments中的一行，那么employees中的相应行也会级联删除。

·Deletions that set null（父表删除，子表该列设为null值）

  当父表中行删除时，则子表的对应foreign key 值设为null。

  举个例子：departments表中的一行删除了，子表employees中对应的foreign key将设为null。

下面表显示了，对父表，子表的DML 分别会有什么后果

DML Statement	对于父表的影响	对于子表的影响
INSERT	如果父键（referenced key）是唯一的话，那么总是可以。	只有在外键相对应的referenced key存在这个数据是，才可以插入。或者外键就是有列值是null 也可以插入。
UPDATE NO ACTION	更新后不会对子表的行有影响时，才可以。	如果更新后的值依然有对应的父键（referenced key）时，那么可以。
DELETE NO ACTION	只有在子表中没有对应行时，才可以删除。	Always OK
DELETE CASCADE	Always OK	Always OK
DELETE SET NULL	Always OK	Always OK

   注意：其他关联动作在oracle的外键完整性约束中没有支持。不过可以通过触发器自己去实现。

 

Indexes and Foreign Keys（索引和外键）

通常，外键要建索引。只有一种情况例外，那就是你引用的 主键或者唯一键永远都不更新，那么你可以不建。

对外键进行索引提供下面两个好处：

·子表的全表锁。替代为对需要的行进行加锁。

·消除对子表的全表扫描。例如，假设用户在departments中删除部门10.如果employees.department_id没有索引，那么数据库必须对employees表进行全表扫描，来看看是否有雇员的department是10

 

CheckConstraints（Check约束）

列或者列集的Check约束，要求表中每行，列中的值要符合这个约束，或者是null。

如果DML操作返回的结果 不符合这个约束，那么这个SQL语句会rollback

CheckConstraint最主要是可以自己定于约束的规则。举个李总，你可以使用check constraints来强制保证下面这些规则在employees表实行：

·salary列的值不能有大于10000的值（太nm扣了）

·commission列的值不能大于salary的值。

下面的案例在employees表上创建了一个最大salary约束，演示一下如果用户插入的值超过这个限制，会出现什么情况：

SQL> ALTERTABLE 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 line1:

ORA-02290:check constraint (HR.MAX_EMP_SAL) violated

一个列可以有多个check约束。举个例子，salary列可以有一个约束来预防值超过10000的再有一个约束来预防值小于500的。

如果多个check约束存在一列上，它们必须被合理设计，保证它们的目的不会发生冲突。多个条件的验证顺序是不一定的（不是先来后到）

数据库本身不会去验证这些条件是否是互斥的。

 

States of Integrity Constraints(完整性约束的状态)

作为约束定义的一部分，你可以指定oracle数据库，什么时间，以及怎样 执行约束，从而确定约束状态。

Checks forModified and Existing Data(已经存在的和新数据的检查)

数据库是你可以指定约束是应用在已经存在的数据，还是未来进入的数据。

如果约束的状态是enabled，那么数据库检查新数据（无论是插入的 还是update的）。数据不符合约束，则不能进入数据库。举个例子，employees.department_id列的NOT NULL约束是enabled的，那么它保证任何新数据都必须有department_id。

如果约束是disabled，表可以包含不符合约束的数据。

你可以将约束的状态设为验证（VALIDATE）或者不验证（NOVALIDATE）已经存在的数据。

如果指定为VALIDATE，那么存在的数据必须符合约束，举个例子，NOT NULL约束设为VALIDATE那么所在列之前已经存在的数据必须符合约束。

如果设定为NOVALIDATE，那么已经存在的数据将不需要符合约束。

VALIDATE和NOVALIDATE的行为 取决于约束是enabled还是disabled

下面的对之间的关心进行了总结：

Modified Data	Existing Data	Summary
ENABLE	VALIDATE	已经存在的和将要进入的数据必须都符合constraint，如果这个约束是新添加的，而表中的数据有不符合的，那么添加约束的操作将报错
ENABLE	NOVALIDATE	数据库对约束进行检查，但不需要符合，因此，已经存在的行可以不符合约束。但是新的或者update的数据必须符合规则
DISABLE	VALIDATE	数据库将约束禁用，删除对应索引，防止对有约束的列进行修改（不允许插入，更新删除）
DISABLE	NOVALIDATE	不检查，也不需要符合。

 

DeferrableConstraints（可延迟约束）

每种约束都可以是可延迟 或者不可延迟（默认），这个状态什么时候Oracle来检查约束是否符合。下面图描述了可延迟约束的选项

Nondeferrable Constraints(非延迟约束)

如果约束是非延迟的，Oracle数据库不会将检查延迟到事务结束。改为在每个语句结束时检查。如果约束被违反，则这个语句rollback。

举个例子，假设你创建了非延迟的NOT NULL约束。如果用户打算插入一列null值，那么数据库会立即对这条语句进行回滚，因为NOT NULL约束违反了。

DeferrableConstraints（可延迟约束）

可延迟约束允许一个事务使用SET CONSTRAINT子句来延迟约束的检查，直到COMMIT。如果你做了一些修改，它们可能违犯了约束，那么这个设置可以有效的使你暂时使约束失效，直到所有的改变完成时 再一次检查。

你可以设置当数据库对可延迟索引检查时默认的动作，你可以指定下面两种：

·INITIALLY IMMEDIATE

  数据库在每个语句结束时即刻检查。如果约束被违反，那么数据库回滚这条语句。

·INITIALLY DEFERRED

  数据库在COMMIT发布时才会检查约束，如果约束违反，那么数据库回滚这个事务。

假设可延迟约束NOT NULL在employees.last_name 列设置，且为INITIALLY DEFERRED。一个用户创建了一个事务，插入了100行，有一些行的last_name列是null值，当用户提交时，那么数据库会把这100行都回滚掉。可是，如果约束设为INITIALLY IMMEDIATE，那么数据库就不会回滚掉整个事务。

如果约束导致一个动作，那么数据库会认为这个动作是导致约束生效的这个语句的一部分，无论约束是deferred 还是immediate的。

举个例子：在departments删除一行导致了employees表的行也级联删除了。这种情况，删除employees行的这个操作会被认为是 删除departments表的DELETE语句的一部分。

 

Examplesof Constraint Checking（约束检查的例子）

一些例子可能会帮助你理解Oracle数据库约束检查，假设如下：

·employees表自身的结构。

·有一个自引用约束使得manager_id列的值引用了employee_id列的值

Insertionof a Value In a Foreign Key Column When No Parent Key Value Exists(在外键中插入数据，而这个值在父键不存在)

想象一下employees表的第一行数据的插入。现在没有行存在，那么怎样才能对manager_id进行插入呢？现在manager_id所引用的employee_id列可是没有任何值的：

·null值可以插入到manager_id列，当然是没有not null 约束的情况下

  因为null值是可以插入到外键中的，那么这行就插入到表里了

·employee_id和manager_id列的值相同时，也可以插入。

  这个案例揭示了Oracle数据库执行约束检查，是在语句完全执行运行之后。

  为了在使 在父键和外键相同的情况下可以插入，数据库必须先运行语句（在这里，就是插入一个新行）然后确认表中是否有employee_id可以让新行的manager_id所匹配。

·一个多行插入的INSERT语句，如INSERT 加SELECT语句，多行中如果有互相匹配的，那么就可以插入。

  举个例子，插入的第一行可能employee_id是200，manager_id是300，而第二行的employee_id是300，而manager_id是200.约束会延迟到这个语句彻底完成后才去检查。

  这时候，所有的行都是第一次插入，但是所有的行都通过了这个约束检查

列的default值会在语句解析前就并入到INSERT语句中。因此，默认列值是收到所有完整性约束的检查。

 

AnUpdate of All Foreign Key and Parent Key Values(同时了更新了所有的外键和父键的值)

考虑相同的自引用完整性约束，在一个完全不同的情况。一个公司被卖掉了,因为这个,所有的员工编号必须更新为和现在公司的员工编号一样(旧的编号加上5000)。因为经理编号，和员工编号一样（看下图），那么经理编号也必须同样的增加5000

你可以执行下面的语句去更新值：

UPDATE employees SET employee_id =employee_id + 5000,

                    manager_id = manager_id +5000;

虽然约束的定义是 每个manager_id要匹配 employee_id，但是前面的语句一样是可以成功，因为数据库实际上是在整个语句完成以后才执行检查。下图显示了数据库在执行检查之前，先执行了整个语句。

这些案例说明了在INSERT UPDATE时的约束的检查机制，但是数据库对于所有的DML操作都使用同样的机制，而且所有的约束都使用相同的机制，并不只是自引用完整性约束。