维护数据完整性（三）-优快云博客

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/145187600

立即检查的（IMMEDIATE）或者延迟检查的（DEFERRED）

使用 SET CONSTRAINTS 语句

SET CONSTRAINTS语句可以用来在事务内部改变约束的检查时机。以下是如何使用该语句的示例：

SET CONSTRAINTS constraint_name DEFERRED; #  -- 将特定约束设置为延迟
SET CONSTRAINTS ALL DEFERRED;   # -- 将所有约束设置为延迟
SET CONSTRAINTS constraint_name IMMEDIATE;   #  -- 将特定约束设置为立即
SET CONSTRAINTS ALL IMMEDIATE;  # -- 将所有约束设置为立即

在这里，constraint_name是你想要设置的约束的名称。如果你想对当前会话中的所有约束进行操作，可以使用ALL。

使用 ALTER SESSION 语句

ALTER SESSION语句可以用来为整个会话设置约束的默认行为。以下是如何使用该语句的示例：

-- 将所有新约束在会话中设置为延迟
ALTER SESSION SET CONSTRAINTS DEFERRED;
-- 将所有新约束在会话中设置为立即
ALTER SESSION SET CONSTRAINTS IMMEDIATE;

请注意，这些语句的具体语法可能会根据你所使用的数据库系统而有所不同。例如，在Oracle数据库中，你可以使用ALTER SESSION来设置约束的行为，但在其他数据库系统（如PostgreSQL或MySQL）中，可能不支持这种用法。
在PostgreSQL中，你可以这样使用SET CONSTRAINTS：

-- 开始一个事务
BEGIN;
-- 将所有约束设置为延迟
SET CONSTRAINTS ALL DEFERRED;
-- 执行一些DML操作
-- ...
-- 提交事务，此时将检查所有延迟的约束
COMMIT;

检查主键（PK）约束和唯一性（UK）约束的过程

主键约束（PK）和唯一性约束（UK）启用：
- 如果约束被启用，数据库将确保表中的数据满足约束条件。
索引可用性：
- 主键和唯一性约束通常依赖于索引来快速检查数据的唯一性。
约束是否可延迟：
- 如果约束是可延迟的，则检查会在事务提交时进行，而不是在每次数据修改时立即进行。
  以下是对上述检查点的详细解释：

PK & UK Enforcement Key enabled? (Yes)
- 启用了主键和唯一性约束的强制检查。
Is an index available for use? (Yes)
- 对于主键和唯一性约束，通常会有一个对应的索引，用于快速查找和验证唯一性。
Constraint Deferrable? (Yes)
- 约束是可延迟的，这意味着在事务提交之前，不立即强制执行约束。
Is the index nonunique? (No)
- 索引不是非唯一的，这意味着索引支持唯一性约束。
  基于上述信息，以下是对检查过程的决策树：
Use existing Constraint deferrable? (Yes)
- 如果现有约束是可延迟的，并且已经符合要求，那么可以继续使用它。
Do not use index
- 如果不应该使用索引，则可能是因为数据库优化或特定的数据操作需求。
Create unique index
- 如果没有为唯一性约束创建索引，则需要创建一个唯一索引来支持该约束。
Create nonunique index
- 如果需要，但通常对于主键和唯一性约束，你不会创建一个非唯一索引。

Oracle数据库服务器在实施主键（Primary Key）和唯一键（Unique Key）约束时使用的流程。

主键和唯一键是通过索引来强制实施的。你可以控制用于强制这些约束的索引的位置和类型。
Oracle服务器使用以下步骤来实现唯一和主键约束：

如果约束被禁用，则不需要索引。
如果约束被启用，并且约束中的列是某个索引的前导部分（即索引的第一个或前几个列），那么无论该索引本身是唯一索引还是非唯一索引，都将使用该索引来强制约束。
如果约束被启用，但没有索引使用约束列作为索引的前导部分，那么将根据以下规则创建一个与约束同名的索引：

如果键是可延迟的（deferrable），则在键列上创建一个非唯一索引。
如果键是不可延迟的（nondeferrable），则创建一个唯一索引。

如果有可用的索引，并且约束是不可延迟的，那么使用现有的索引。
如果约束是可延迟的，并且索引是非唯一的，那么使用现有的索引。
简而言之，这段文字说明了Oracle数据库如何根据约束的类型（主键或唯一键）和属性（是否可延迟）来决定是否使用现有索引或创建新索引，以确保数据的唯一性和完整性。

在这里插入图片描述

展示了在数据库管理系统中关于主键（PK）和唯一键（UK）的实施流程。具体步骤如下：

Key enabled?：首先检查键是否已启用。
- 如果否，则不使用索引。
Is an index available for use?：接下来检查是否有可用的索引。
- 如果没有，继续判断约束是否可以延迟（Constraint deferrable?）。
  - 如果可以延迟，则创建唯一索引（Create unique index）。
  - 如果不可以延迟，则不使用索引（Do not use index）。
Constraint Deferrable?：如果存在可用索引，进一步检查约束是否可以延迟。
- 如果可以延迟，然后检查索引是否为非唯一（Is the index nonunique?）。
  - 如果是非唯一，则使用现有索引（Use existing index）。
  - 如果不是非唯一，则创建非唯一索引（Create nonunique index）。
    整个流程旨在确定如何管理和实施主键和唯一键约束，以确保数据的完整性和高效查询。

创建表的例子。

CREATE TABLE hr.employee (
  id NUMBER(7)
    CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX 
    STORAGE (INITIAL 100K NEXT 100K)
    TABLESPACE ind,
  last_name VARCHAR2(25)
    CONSTRAINT employee_last_name_nn NOT NULL,
  dept_id NUMBER(7)
)
TABLESPACE users;

CREATE TABLE hr.employee: 这是在hr模式（schema）下创建一个名为employee的表。
id NUMBER(7): 定义了一个名为id的列，数据类型为NUMBER，最多可以存储7位数字。
CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX: 这是在id列上定义了一个名为employee_id_pk的主键约束。DEFERRABLE关键字意味着这个约束的检查可以延迟到事务结束时进行，而不是在每次插入、更新或删除操作后立即检查。
USING INDEX: 指定应该为这个主键约束创建一个索引。
STORAGE (INITIAL 100K NEXT 100K): 这是对索引存储的初始和后续扩展参数的设置。INITIAL 100K表示索引最初分配的存储空间是100KB，而NEXT 100K表示当初始空间用尽时，每次扩展分配的空间是100KB。
TABLESPACE ind: 指定索引应该存储在名为ind的表空间中。
last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL: 定义了一个名为last_name的列，数据类型为VARCHAR2，最大长度为25个字符。NOT NULL约束确保这个列不能有NULL值。
dept_id NUMBER(7): 定义了一个名为dept_id的列，数据类型为NUMBER，最多可以存储7位数字。
TABLESPACE users: 指定表的数据应该存储在名为users的表空间中。
总的来说，这段代码创建了一个表，定义了主键约束，并指定了索引的存储参数和表空间。主键约束被设置为可延迟，这意味着在事务中，对主键的检查可以推迟到事务提交时进行，这有助于提高并发事务的性能。同时，通过指定索引的存储参数，可以优化索引的性能和存储需求。

设置主键和唯一约束的指导原则

Place indexes in a separate tablespace.
- 将索引放在单独的表空间中。这样做可以有助于提高性能和管理效率。例如，可以将索引放在快速的存储介质上，而将表数据放在其他存储上。此外，如果索引表空间需要维护（如备份或恢复），它不会影响到表数据的表空间。
Use nonunique indexes if bulk loads are frequent.
- 如果经常进行批量加载操作，使用非唯一索引。在批量加载过程中，唯一索引可能会因为需要检查唯一性而降低插入性能。非唯一索引可以加快批量加载的速度，因为它们不需要在每次插入时都进行唯一性检查。但是，这仅适用于那些不会违反唯一性约束的批量加载操作。
Self-referencing foreign keys: Define or enable foreign keys after the initial load.
- 对于自引用外键（即一个表中的外键引用同一个表中的主键），在初始数据加载之后定义或启用外键约束。这样做可以避免在加载大量数据时外键约束检查造成的性能开销。一旦数据加载完成，再启用外键约束可以确保数据的引用完整性。
Defer constraint checking.
- 延迟约束检查。这意味着在事务执行期间，可以暂时不检查约束，直到事务提交时才进行。这可以提高包含大量数据修改操作的事务的性能，因为它减少了在事务执行期间需要进行的约束检查次数。
  总的来说，这些指导原则旨在帮助数据库管理员和开发人员在设计数据库时做出优化决策，以提高数据库的性能和可维护性。通过合理地设置索引、处理批量加载、管理自引用外键和延迟约束检查，可以有效地处理数据完整性要求，同时保持良好的数据库性能。

Oracle数据库中使用`EXCEPTIONS`表，处理在启用表约束时出现的违反约束的数据

创建EXCEPTIONS表：
- 通过运行utlexpt1.sql脚本来创建EXCEPTIONS表。这个脚本通常位于Oracle数据库的rdbms/admin目录下，用于创建一个表，该表可以存储违反约束的记录信息。
```
@?/rdbms/admin/utlexpt1.sql
```
@ 后跟一个文件路径时，SQL*Plus会执行该路径指定的SQL脚本。
- ? 符号是一个代字符，代表当前Oracle数据库的安装目录。当你在命令中使用 ? 时，SQL*Plus会用Oracle的安装目录替换它。
  如果Oracle数据库安装在 /u01/app/oracle/product/12.2.0/dbhome_1 目录下，那么上述命令实际上会执行 /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlexpt1.sql 脚本。
  如果你不知道Oracle数据库的确切安装路径，使用 ? 符号可以让SQL*Plus自动找到正确的路径。
执行带有EXCEPTIONS选项的ALTER TABLE语句：
- 当你尝试启用一个表上的约束时，可以使用ALTER TABLE语句的EXCEPTIONS选项来捕获并存储违反该约束的行。
```
ALTER TABLE your_table ENABLE CONSTRAINT your_constraint WITH EXCEPTIONS;
```
在EXCEPTIONS表上使用子查询来定位带有无效数据的行：
- 一旦创建了EXCEPTIONS表，并且使用WITH EXCEPTIONS选项启用了约束，你就可以查询EXCEPTIONS表来找到违反约束的行。
```
SELECT e.* FROM EXCEPTIONS e;
```

纠正错误：

根据从EXCEPTIONS表中获取的信息，你可以定位并更正导致约束违反的数据。

-- 假设我们发现违反约束的行是因为重复的主键
UPDATE your_table SET id = new_value WHERE id = old_value;

重新执行ALTER TABLE以启用约束：
- 在纠正了所有违反约束的数据后，再次执行ALTER TABLE语句来启用约束。
```
ALTER TABLE your_table ENABLE CONSTRAINT your_constraint;
```

示例

假设我们有一个表employees，其中有一个名为employee_id的主键列，我们想要启用该列的主键约束，但是有一些重复的employee_id。

-- 创建EXCEPTIONS表
@?/rdbms/admin/utlexpt1.sql
-- 尝试启用约束，并捕获异常
ALTER TABLE employees ENABLE CONSTRAINT emp_pk WITH EXCEPTIONS;
-- 查询EXCEPTIONS表以找到违反约束的行
SELECT e.* FROM EXCEPTIONS e;
-- 假设我们找到了违反约束的行，现在我们更正数据
UPDATE employees SET employee_id = new_unique_value WHERE employee_id = duplicate_value;
-- 重新启用约束
ALTER TABLE employees ENABLE CONSTRAINT emp_pk;