完整性约束-CSDN博客

本文链接：https://blog.csdn.net/pengshulin213/article/details/53178028

数据完整性简介

业务规则会指定一些条件和关系，它们要么必须始终为真，要么必须始终为假。例如，每个公司会定义其有关工资、雇员人数、库存跟踪，等方面的政策。保持数据的数据完整性很重要，它由数据库管理员或应用程序开发人员来确定，以遵从业务规则。

用于保证数据完整性的技术

在设计数据库应用程序时，开发人员有多种选项用于保证存储在数据库中的数据的完整性。这些选项包括：

 通过数据库触发器存储过程，强制实施业务规则

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

 在数据库应用程序的代码中执行业务规则

 使用Oracle完整性约束，它们是定义在列级或对象级上的，用来限制数据库中的值的规则

完整性约束的优势

完整性约束是一个模式对象，它使用 SQL来创建和删除。若要强制实施数据完整性，请尽可能使用完整性约束。相对于其他强制数据完整性替代方案，完整性约束包括如下优点：

 容易声明

在您定义或更改表时，使用 SQL 语句定义完整性约束，而无需任何额外的编程。SQL 语句易于编写，也易于排除编程错误。

 集中化的规则

完整性约束定义在表上，并存储在数据字典中。因此，由所有应用程序输入的数据都必须遵守相同的完整性约束。如果约束规则在表级发生了更改，应用程序不需要变更。此外，甚至在数据库检查 SQL 语句之前，应用程序就可以使用数据字典中的元数据立即告知用户的违例行为。

 加载数据时的灵活性

当加载大量数据时，您可以暂时禁用完整性约束，以避免性能开销。当数据加载完成后，您可以重新启用完整性约束。

完整性约束的类型

Oracle 数据库使您能够在表级或列级应用约束。作为列或属性定义的一部分而指定的约束，称为行内规范约束。作为表定义的一部分而指定的约束称为行外规范约束。

好几种类型的完整性约束定义中都使用键这个术语。键是某种类型的完整性约束的定义中包含的列或列集。键描述关系数据库中的表与列之间的关系。键中的单个值称为键值。

表 5-1 描述了约束的类型。除NOT NULL必须指定为行内规范，其它每一个都可以指定为行内或行外规范。

这里写图片描述

非空完整性约束

NOT NULL 约束要求表中的列不包含空值。空值即值的缺失。默认情况下，一个表中的所有列都允许空值。

关于default 和 not null 和 default default_value not null：

create table test (col1 number default 0, col2 number constraint nn_col2  not null, col3 number);

alter table test modify col3  default 0 constraint nn_col1 not null;

--再往表中插入值时，如果不显式指定col2列的值，会提示错误：
SQL> insert into test(col1, col3) values (1, 1);
insert into test(col1, col3) values (1, 1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST"."COL2")

--如果只指定了col2列的值，col1，col3列使用默认值：
SQL> insert into test (col2) values (1);

1 row created.

SQL> select * from test;

      COL1   COL2       COL3
---------- ---------- ----------
     0      1          0

--由于col3列有not null完整性约束，不允许空值：
SQL> insert into test (col1, col2, col3) values ('', 1, '');
insert into test (col1, col2, col3) values ('', 1, '')
                                                   *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST"."COL3")

not null 与 default default_value not null主要区别在于：
虽然它们都不允许空值插入，但如果没有显式指定列值的话：default default_value not null 列可以插入默认值，而由于not null 只能插入空值，所以不允许插入。

唯一键约束

唯一键约束要求在一个列或列集中的每个值是唯一的。在一个表中，不允许多个行在有唯一键约束的列（唯一键）或列集（复合唯一键）上具有重复值。

注意：

术语键仅指在完整性约束中定义的列。因为数据库通过在键列上隐含创建或重用索引来强制执行唯一性约束，术语唯一键有时会被错误地用作唯一键约束或唯一索引的同义词。

唯一键约束适合于任何不允许重复值的列。唯一约束与主键约束不同，主键的目的是唯一地标识表中的每一行，通常它只要求唯一，而并不一定要有什么实际意义。

除非也指定了NOT NULL 约束，否则空值也始终满足唯一键约束。因此，典型的情况是在列上同时具有唯一键约束和非空约束。这种组合强制用户输入的是唯一值，并消除新行数据与现有行数据发生冲突的可能性。

唯一键约束示例：

--创建表时，指定唯一性完整性约束：
create table test (col1 number default 0, col2 number constraint uk_col2  unique);

--创建表后，指定唯一性完整性约束：
alter table test add constraint uk_col1 unique(col1);

注意：

鉴于在多个列上的唯一键约束的搜索机制，对含有部分空值的复合唯一键约束中，在非空列中不能有相同的值。

主键约束

在一个主键约束中的列或列集，其值能唯一地标识行。每个表只能有一个主键，起到确定行的作用，并确保不存在任何重复的行。

主键可以是自然键或代理键。自然键是由表中的现有属性组成的一个有意义的标识符。例如，一个自然键可能是查找表中的邮政编码。相比之下，代理键是一个系统生成的递增标识符，以确保在一个表中的唯一性。通常，由一个序列生成代理键。

Oracle数据库实现的主键约束可以保证如下行为：

 任何两行在指定的列或列集上都不具有重复值。

 主键列不允许空值。

数据库使用索引来强制主键约束。通常，在某列上创建的主键约束会隐含创建一个唯一索引和一个非空约束。但请注意，此规则有如下例外情况：

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

--如果可延迟约束类型是：initially deferred ：
create table test (col1 number constraint pk_col1 primary key initially deferred, col2 number constraint uk_col2  unique);

--可以看到index是NONUNIQUE
SQL> select index_name, index_type, uniqueness from user_indexes where index_name = upper('pk_col1');

INDEX_NAME             INDEX_TYPE          UNIQUENES
------------------------------ --------------------------- ---------
PK_COL1                NORMAL              NONUNIQUE


--如果可延迟约束类型是：initially immediate：
create table test (col1 number constraint pk_col1 primary key initially immediate, col2 number constraint uk_col2  unique);

--此时index类型是UNIQUE
SQL> select index_name, index_type, uniqueness from user_indexes where index_name = upper('pk_col1');

INDEX_NAME             INDEX_TYPE          UNIQUENES
------------------------------ --------------------------- ---------
PK_COL1                NORMAL              UNIQUE

注意：

您可以使用CREATE UNIQUE INDEX 语句，显式创建一个唯一索引。

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

create table test (col1 number , col2 number constraint uk_col2  unique);

create index ind_col1 on test (col1);

alter table test add constraint pk_col1 primary key (col1);

--产看之前创建的index类型NONUNIQUE：
SQL> select index_name, index_type, uniqueness from user_indexes where index_name = upper('ind_col1');

INDEX_NAME             INDEX_TYPE          UNIQUENES
------------------------------ --------------------------- ---------
IND_COL1               NORMAL              NONUNIQUE

--查看由primary key 创建的 index，会发现并没有创建索引：
SQL> select index_name, index_type, uniqueness from user_indexes where index_name = upper('pk_col1');

no rows selected

这里说个题外话，之前看到有人问有关唯一性索引和唯一性约束的如何选择的问题，从表面上看这两个都实现了约束和索引的功能，但它们在管理上还是有所不同，下面在示例中会说明：

--创建测试表
create table test (col1 number, col2 number);

--在col1添加唯一性索引
create unique index ind_col1 on test(col1);

--在col2添加唯一性约束
alter table test add constraint uk_col2 unique(col2);

--查看唯一性索引相关信息
select index_name, index_type, uniqueness from user_indexes where index_name = 'IND_COL1';
--1 IND_COL1    NORMAL  UNIQUE

--查看该唯一性索引是否隐式创建了约束，从数据字典发现没有
select constraint_name, constraint_type from user_constraints where constraint_name = 'IND_COL1';

--查看唯一性约束相关信息
select constraint_name, constraint_type from user_constraints where constraint_name = 'UK_COL2';
--1 UK_COL2 U

--查看该唯一性约束是否隐式创建了索引，发现创建了唯一性索引
select index_name, index_type, uniqueness from user_indexes where index_name = 'UK_COL2';
--1 UK_COL2 NORMAL  UNIQUE

--使UK_COL2索引不可用，发现是可以调整该索引的相关属性的
SQL> alter index UK_COL2 unusable;

Index altered.

--重建UK_COL2索引
SQL> alter index UK_COL2 rebuild;

Index altered.

--此时如果删除UK_COL2索引，会提示如下错误信息
SQL> drop index UK_COL2 ;
drop index UK_COL2
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

外键约束

只要两个表包含一个或多个公共列，则数据库可以通过一个外键约束（也称为参照完整性约束）来强制建立两个表之间的关系。该约束要求定义约束的列中的每个值，必须与另一个指定表中的指定列中的值相匹配。参照完整性规则的一个例子是，雇员只可以为一个现有的部门工作。

外键

约束定义中包含的列或列集，它引用了参考键。

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

注：外键与被引用键不必同名称。

外键的值，可以要么匹配被引用主键或唯一键的值，要么为空。如果一个复合外键的任何列为空，则该键的非空部分不一定要匹配父项中的任何相应部分。

被引用键

被外键所引用的表中的唯一键或主键。

注：如果被引用键是唯一键的话，要显式指出。

依赖表或子表

包含外键的表。此表依赖于父表中被引用的唯一键或主键的值

被引用表或父表

由子表的外键引用的表。正是该表中的被引用值决定了在子表中特定的插入或更新是否可被允许。

自引用完整性约束

在这种情况下，外键引用同一个表中的父键。

空值和外键

关系模型允许外键的值可以匹配被引用主键或唯一键值，或者为空。

如果一个复合外键的任何列为空，则该键的非空部分不一定要匹配父项中的任何相应部分。

父键修改和外键

删除父键会影响外键和父键之间的关系。

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

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

在正常的情况下，如果结果会违反参照完整性，用户不能修改被引用的键值。

 级联删除

级联删除(DELETE CASCADE)即是当包含被引用键值的行被删除时，导致子表中的所有的外键依赖值所在行也会被删除。

foreign key(column_name) references table_name(column_name) on delete cascade;

 对删除置空

删除置空(DELETE SET NULL)即是当包含被引用键值的行被删除时，导致子表中的所有的外键依赖值被全部置空。

foreign key(column_name) references table_name(column_name) on delete set null;

注意：

其他Oracle数据库外键完整性约束不支持的引用性动作，可以使用数据库触发器强制执行。

索引和外键

作为一条规则——总是应该为外键编制索引。唯一的例外是，当匹配的唯一键或主键永远不会被更新或删除时。在子表中的外键上建立索引提供了下列好处：

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

 消除在子表上进行全表扫描的需要。

检查性约束

在一个列或列集上的检查约束，要求所指定的条件为真，或对每一行来说是未知的。如果DML语句导致约束条件的计算结果为假，则 SQL 语句将被回滚。

检查约束的主要好处是，具备强制执行非常具体的完整性规则的能力。

单个列可以在其定义中包括多个检查性约束。

如果在某列上存在多个检查约束，则他们必须被合理设计，保证他们的目的不会发生冲突。不能假定多个条件计算之间的顺序。数据库不会验证这些检查条件是否相互排斥。

create table test (col1 number check (col1 > 0), check (col1 <0));

这样的表也是被允许创建的。

完整性约束的状态

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

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

数据库使您可以指定将某个约束应用于现有数据还是应用于新数据。如果约束是启用的，则当输入或更新数据时，数据库会检查新的数据。不符合该约束的数据不能输入到数据库。例如，对 employees.department_id 启用 NOT NULL 约束，可以保证新插入的每一行都有一个部门 id。如果约束是禁用的，则表可能会包含违反约束的行。

可以设置约束来验证（VALIDATE）或不验证 (NOVALIDATE) 现有数据。如果指定了 VALIDATE ，则现有数据必须符合该约束。例如，对 employees.department_id启用 NOT NULL 约束并将其设置为 VALIDATE，则会检查每个现有行都有一个部门 id。如果指定了 NOVALIDATE，则现有数据不需要符合该约束。

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

这里写图片描述

注：如果约束是disable，由约束创建的索引被删除。

可延迟约束

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

这里写图片描述

不可延迟约束

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

可延迟约束

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

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

 INITIALLY IMMEDIATE

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

 INITIALLY DEFERRED

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

SQL> create table test (col1 number, col2 number, col3 number, col4 number);

Table created.

SQL> alter table test add constraint uk_col1 unique(col1) not deferrable;

Table altered.

SQL> alter table test add constraint uk_col2 unique(col2) initially immediate;

Table altered.

SQL> alter table test add constraint uk_col3 unique(col3) deferrable initially immediate;

Table altered.

SQL> alter table test add constraint uk_col4 unique(col4) initially deferred;

Table altered.

SQL> alter table test modify constraint uk_col3 initially deferred;

Table altered.

not deferrable 与 initially immediate相同。
initially immediate 与 deferrable initially immediate区别：
后者可以转换为 initially deferred，前者不可以。