暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

Oracle Concepts(Oracle 19c):05 Data Integrity

原创 Ryan Bai 2021-08-27
457

数据完整性简介

保持数据的数据完整性很重要, 它由数据库管理员或应用程序开发人员来确定, 以遵从业务规则。

业务规则会指定一些条件和关系,它们要么必须始终为真,要么必须始终为假。例如, 每个公司会定义其有关工资、 雇员人数、 库存跟踪,等方面的政策。

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

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

这些选项包括:

  • 通过数据库触发器存储过程, 强制实施业务规则
  • 使用存储过程完全控制数据访问
  • 在数据库应用程序的代码中执行业务规则
  • 使用 Oracle 完整性约束,它们是定义在列级或对象级上的,用来限制数据库中的值的规则

完整性约束的优势

完整性约束是一个模式对象, 它使用 SQL 来创建和删除。若要强制实施数据完整性, 请尽可能使用完整性约束。

相对于其他强制数据完整性替代方案, 完整性约束包括如下优点:

  • 容易声明
    在您定义或更改表时, 使用 SQL 语句定义完整性约束, 而无需任何额外的编程。 SQL 语句易于编写,也易于排除编程错误。
  • 集中化的规则
    完整性约束定义在表上,并存储在数据字典中。因此,由所有应用程序输入的数据都必须遵守相同的完整性约束。如果约束规则在表级发生了更改, 应用程序不需要变更。此外,甚至在数据库检查 SQL 语句之前, 应用程序就可以使用数据字典中的元数据立即告知用户的违例行为。
  • 加载数据时的灵活性
    当加载大量数据时, 您可以暂时禁用完整性约束, 以避免性能开销。当数据加载完成后, 您可以重新启用完整性约束。

完整性约束的类型

Oracle 数据库使您能够在表级或列级应用约束。

作为列或属性定义的一部分而指定的约束, 称为行内规范约束。 作为表定义的一部分而指定的约束称为行外规范约束。

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

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

约束类型 描述 另见
NOT NULL 允许或不允许在指定的列插入或更新包含空值的行。 NOT NULL Integrity Constraints
Unique key 在相同的列、或多个列的组合中,不允许多个行具有相同的值,但允许一些值为空。 Unique Constraints
Primary key 合并 NOT NULL 约束和唯一性约束。 在相同的列、或多个列的组合中,不允许多个行具有相同的值,也不允许为空。 Primary Key Constraints
Foreign key 指定一个列作为外键,在外键和主键或唯一键之间建立关系,也称为引用键。 Foreign Key Constraints
Check 要求某个数据库值服从指定的条件。 Check Constraints
REF 规定对 REF 列中的值上的允许的数据操作类型,这些操作如何影响依赖值。 在一个对象-关系的数据库中, 一个叫做 REF 的内置数据类型, 封装了对一个某种指定对象类型的行对象的引用。 在 REF 列上的引用完整性约束确保该 REF 有一个行对象。 Oracle Database Object-Relational Developer’s Guide”来了解 REF 约束

NOT NULL 完整性约束

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

NOT NULL 约束主要用于不能缺少值的列。 例如, hr.employees 表在last_name 列上需要有值。试图插入一个没有姓氏的雇员行会生成一个错误:

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith'); . . . ERROR at line 1: ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")
复制

只有在表不包含任何行或指定默认值的情况下,才能给列添加NOT NULL约束。

Unique 约束

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

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

  • 客户电话号码, 其主键是客户号
  • 部门名称, 其主键是部门编号

如示例 2-1 所示, 在 hr.employees 表的 email 列上存在一个唯一键约束。相关部分的语句如下所示:

CREATE TABLE employees ( ... , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL ... , CONSTRAINT emp_email_uk UNIQUE (email) ... );
复制

如下例所示,emp_email_uk 约束可以确保没有任何两名雇员具有相同的电子邮件地址。

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY'; EMPLOYEE_ID LAST_NAME EMAIL ----------- ------------------------- ------------------------- 202 Fay PFAY SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) 1 VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK'); . . . ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
复制

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

例 5-1 Unique 约束

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY'; EMPLOYEE_ID LAST_NAME EMAIL ----------- ------------------------- ------------------------- 202 Fay PFAY SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) 1 VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK'); . . . ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
复制

Primary Key 约束

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

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

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

  • 任何两行在指定的列或列集上都不具有重复值。
  • 主键列不允许空值。

需要用到主键的典型情况是雇员的编号标识。每个员工必须具有唯一的 id。一名雇员只能由 employees 表中的有且仅有的一行来描述。

唯一键约束示例指示一个雇员 ID 为 202 的现有员工, 其雇员 ID 是主键。下面的示例显示了试图添加一名具有相同雇员 ID 的雇员,和一名没有雇员 ID 的雇员:

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) 1 VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK'); . . . ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated SQL> INSERT INTO employees (last_name) VALUES ('Chan'); . . . ERROR at line 1: ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")
复制

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

  • 有时,当您使用一个可延迟的约束选项来创建一个主键时,其生成的索引不是唯一的。
  • 当你创建主键约束时,如果有一个现成的索引可用,则该主键约束会重用此索引,而不会隐式创建一个额外的新索引。

默认情况下,隐式创建的索引名称即是主键约束的名称。您也可以为索引指定一个用户定义的名称。您可以通过在用来创建该约束的 CREATE TABLE 或 ALTER TABLE 语句中包括 ENABLE 子句,来为索引指定存储选项。

Foreign Key 约束

只要两个表包含一个或多个公共列, 则数据库可以通过一个外键约束(也称为参照完整性约束)来强制建立两个表之间的关系。

该约束要求定义约束的列中的每个值, 必须与另一个指定表中的指定列中的值相匹配。参照完整性规则的一个例子是, 雇员只可以为一个现有的部门工作。

下表列出了与参照完整性约束相关的术语。

术语 定义
Foreign key 约束定义中包含的列或列集, 它引用了参考键。 例如,employees 表中的 department_id 列是一个外键,它引用了 departments 表中的 department_id 列。
外键可以定义在多个列上。但是, 复合外键必须引用具有相同数量和相同数据类型列的复合主键或复合唯一键。
外键的值, 可以要么匹配被引用主键或唯一键的值, 要么为空。 如果一个复合外键的任何列为空,则该键的非空部分不一定要匹配父项中的任何相应部分。
Referenced key 被外键所引用的表中的唯一键或主键。 例如,相对于 employees 表中的 department_id 列来说,departments 表中的 department_id 列是被引用键。
Dependent or child table 包含外键的表。此表依赖于父表中被引用的唯一键或主键的值。 例如, employees 表是 departments 表的一个子表。
Referenced or parent table 由子表的外键引用的表。正是该表中的被引用值决定了在子表中特定的插入或更新是否可被允许。 Departments 表是 employees 表的父表。

图 5-1 显示在 employees.department_id 列上的一个外键。它保证在此列中的每个值必须与 departments.department_id 列中的某个值相匹配。因此,在 employees.department_id 列中不会存在错误的部门编号。

图 5-1 Referential Integrity Constraints

自引用完整性约束

自引用完整性约束是一个引用同一个表中的父键为外键的约束

在下图中, 自引用约束可以确保在 employees.manager_id 列中的每个值对应于 employees.employee_id 列中的一个现有值。例如, 员工 102 的经理必须存在于 employees 表中。此约束消除了在 manager_id 列中存在错误的雇员人数的可能性。

图 5-2 单表引用约束

空值和外键

关系模型允许外键的值可以匹配被引用主键或唯一键值,或者为空。例如,用户可以往 hr.employees 表插入行 , 而无需指定一个部门 id。

如果一个复合外键的任何列为空,则该键的非空部分不一定要匹配父项中的任何相应部分。例如,一个 reservation 表可能在 table_id 和 date 列上包含一个复合外键,但是 table_id 是 null。

父键修和外键

删除父键会影响外键和父键之间的关系。例如, 如果用户试图删除某个部门的记录, 那么属于这个部门的雇员记录会发生事情呢?

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

  • 对删除或更新操作,不采取任何动作
    在正常的情况下,如果结果会违反参照完整性, 用户不能修改被引用的键值。例如, 如果 employees.department_id 是对 departments 表的一个外键, 且有一些雇员属于某个特定部门,则试图删除该部门所在的行会违反约束。
  • 级联删除
    级联删除(DELETE CASCADE)即是当包含被引用键值的行被删除时,导致子表中的所有的外键依赖值所在行也会被删除。例如,删除 departments 表中的某行, 会导致这个部门中的所有雇员行也被删除。
  • 对删除置空
    删除置空(DELETE SET NULL)即是当包含被引用键值的行被删除时,导致子表中的所有的外键依赖值被全部置空。 例如,删除部门表中的某行, 会导致将该部门中的所有雇员行的 department_id 列被置为空。

表 5-3 列出了在父表中的键值及子表中的外键值上,针对不同的引用性操作,所允许的 DML 语句。

DML 语句 对父表发出 对子表发出
INSERT 若该父键值是唯一的,则总是可以 只有当外键值存在相应父键、或部分为空、或全为空时才可以
UPDATE NO ACTION 只要该语句在子表中不留下任何这样的行——其外键值在父表中不存在,则允许 只要新的外键值仍可以引用父表中的某个被引用键值,则允许
DELETE NO ACTION 只要子表中没有行引用该父键值,则允许 总是可以
DELETE CASCADE 总是可以 总是可以
DELETE SET NULL 总是可以 总是可以
索引和外键

作为一条规则——总是应该为外键编制索引。唯一的例外是, 当匹配的唯一键或主键永远不会被更新或删除时。

在子表中的外键上建立索引提供了下列好处:

  • 可防止在子表上的全表锁定。相反,数据库只需要在索引上获取一个行锁。
  • 消除在子表上进行全表扫描的需要。作为一个说明, 假定用户从部门表中删除部门 10 的记录。如果没有为 employees.department_id 建立索引, 则数据库必须扫描 employees 表, 以查看是否存在属于部门 10 的雇员。

Check 约束

在一个列或列集上的检查约束, 要求所指定的条件为真, 或对每一行来说是未知的。

如果 DML 语句导致约束条件的计算结果为假, 则 SQL 语句将被回滚。检查约束的主要好处是, 具备强制执行非常具体的完整性规则的能力。例如可以使用检查性约束在 hr.employees 表中强制执行下列规则:

  • 在 salary 列中不能有大于 10000 的值。
  • commission 列必须有一个值,但不能大于 salary。

下面的示例在 employees 表上创建了一个最高薪金约束,并演示了当一个语句尝试插入一个其所包含的薪水超过了最大值的行时,会发生什么情况:

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001); SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary) 1 VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000); . . . ERROR at line 1: ORA-02290: check constraint (HR.MAX_EMP_SAL) violated
复制

单个列可以在其定义中包括多个检查性约束。例如 salary 列可以有一个防止其值超过 10000 的约束, 和另一个可以防止其值小于 500 的约束。

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

完整性约束的状态

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

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

数据库使您可以指定将某个约束应用于现有数据还是应用于新数据。如果约束是启用的,则当输入或更新数据时, 数据库会检查新的数据。不符合该约束的数据不能输入到数据库。

例如, 对 employees.department_id 启用 NOT NULL 约束, 可以保证新插入的每一行都有一个部门 id。如果约束是禁用的,则表可能会包含违反约束的行。

您可以将约束设置为以下两种验证模式之一:

  • 验证
    现有数据必须符合约束。例如,在employees.department_id上启用NOT NULL约束,并将其设置为VALIDATE检查现有的每一行是否具有部门ID。
  • 不验证
    现有数据不需要符合约束。实际上,这是一种“相信我”的模式。例如,如果您确定加载到表中的每个sales都有一个日期,那么您可以在date列上创建一个NOT NULL约束,并将该约束设置为NOVALIDATE。非强制约束通常只对物化视图和查询重写有用。
    对于NOVALIDATE模式下的约束,RELY 参数表明优化器可以使用该约束来确定连接信息。尽管约束不用于验证数据,但它支持对物化视图进行更复杂的查询重写,并支持数据仓库工具从数据字典检索约束信息。默认值为NORELY,这意味着优化器实际上不知道约束。

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

修改数据 现存数据 说明
ENABLE VALIDATE 现有的和将来的数据都必须遵守约束。如果试图在一个已填充的表上应用一个新的约束,而现有的行违反该约束,则会导致一个错误。
ENABLE NOVALIDATE 数据库会检查该约束,但不需要对所有行为真。因此,现有的行可以违反该约束,但新的或修改后的行必须遵守该约束。这种模式通常用于包含已验证完整性的现有数据的数据仓库。
DISABLE VALIDATE 数据库禁用该约束、 删除其上的索引,并防止修改受约束的列。
DISABLE NOVALIDATE 不检查约束,也不需要为真。

当数据库检查约束的有效性时

约束可能处于不可延迟(默认值) 或可延迟两种状态之一。该状态确定数据库何时检查约束的有效性。

下图描述了可延迟约束的选项。

图 5-3 可延迟约束的选项

不可延迟约束

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

例如,假设您在 employees.last_name 列上创建一个不可延迟的 NULL 约束。如果用户试图插入一个没有姓氏的行, 则数据库会立即回滚该语句, 因为违反了 NOT NULL 约束。 在这种情况下, 将不会插入任何行。

可延迟约束

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

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

  • INITIALLY IMMEDIATE
    数据库在每个语句执行后,立即检查约束。如果违反了约束,则数据库回滚该语句。
  • INITIALLY DEFERRED
    发出 COMMIT 时,数据库检查约束。如果违反了约束,则数据库回滚该事务。

假定一个在 employees.last_name 列上的可延迟的 NOT NULL 约束被设置为 INITIALLY DEFERRED。用户创建了一个包含 100 个 INSERT 语句的事务, 其中一些在 last_name 列上具有空值。当用户尝试提交时,数据库将回滚所有 100 个语句。但是,如果这种限制被设置为 INITIALLY IMMEDIATE, 则数据库将不会回滚整个事务。

如果一个约束导致一个动作,则数据库认为此动作是引发它的语句的一部分,而无论该约束是可延迟的或立即的。例如,从 departments 表中删除一行,会导致删除 employees 表中引用了相应的被删部门的雇员。在这种情况下,从 employees 表中删除雇员被认为是对 departments 表执行的 DELETE 语句的一部分。

约束检查的示例

下面的例子可以帮助说明数据库何时执行检查约束。

假定如下:

例子:当父键值不存在时,往外键列中插入值

考虑向 employees 表中插入第一行的情况。 由于当前不存在任何行,那么如果 manager_id 列中的值不能引用在 employee_id 列中的任何现有值,如何才能输入新行呢?

一些可能情况是:

  • 如果 在 manager_id 列上没有定义 NOT NULL 约束,可以为第一行中的 manager_id 列输入一个空值。
    因为在外键中允许使用空值, 此行可以被插入到表中。
  • 如果该雇员是他或她自己的经理, 可以在 employee_id 和 manager_id 列中输入相同的值。
    这种情况表明数据库是在完全运行该语句之后,执行其约束检查的。若要允许输入在父键和外键中具有相同值的行,数据库必须首先运行该语句 (也就是插入新的行),然后再确定表中是否有任何行其 employee_id 对应新行的 manager_id 列。
  • 一个多行的 INSERT 语句, 如一个具有嵌套 SELECT 语句的 INSERT 语句, 可以插入相互引用的多个行。
    例如,第一行可能具有雇员 ID 200 和经理 ID 300,而第二行的雇员 ID 300 和经理 ID 200。约束检查被推迟到整个语句执行完成。首先, 所有行先被插入, 然后对所有行进行违反约束检查。

在语句进行解析之前,其默认值作为 INSERT 语句的一部分被包括在内。因此,默认列值都要遵从所有完整性约束检查。

例:同时更新外键和父键的值

在这个例子中,自引用约束使员工的manager_id列中的条目依赖于employee_id列的值。

假如该公司已出售。由于这次出售, 所有雇员编号必须都更新为其当前值加 5000,以适应新公司的员工编号。如下图所示,部分员工同时也是管理员:

图 5-4 更新前的雇员表

因为经理编号是真正雇员编号,经理编号也必须增加 5000。您可以执行下面的 SQL 语句,以更新其值:

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

虽然已定义了一个约束, 以验证每个 manager_id 值与 employee_id 值匹配, 但前面的语句仍然是合法的,因为数据库在该语句完成后检查约束。图 5-4 显示, 数据库在检查约束之前执行整个 SQL 语句的操作。

图 5-5 约束检查

本节中的示例只说明了在 INSERT 和 UPDATE 语句执行过程中的约束检查机制, 实际上, 数据库在所有类型的 DML 语句中都使用相同的机制。 相同的机制用于所有类型的约束, 而不只是自引用约束。

最后修改时间:2024-04-16 09:21:24
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

目录
  • 数据完整性简介
    • 用于保证数据完整性的技术
    • 完整性约束的优势
  • 完整性约束的类型
    • NOT NULL 完整性约束
    • Unique 约束
    • Primary Key 约束
    • Foreign Key 约束
    • 自引用完整性约束
      • 空值和外键
      • 父键修和外键
      • 索引和外键
    • Check 约束
  • 完整性约束的状态
    • 对现有数据和新数据的检查
    • 当数据库检查约束的有效性时
      • 不可延迟约束
      • 可延迟约束
    • 约束检查的示例