如何管理约束

本文主要介绍如何管理约束，包括决定何时发生约束检查，如何删除约束，删除和更新父行，插入和更新子行。

1. 约束事务模式

约束事务模式决定何时发生引用违例检查。

对于具有日志记录的数据库
— 即时约束（Immediate constraint）检查会在执行完每个语句之后检查违例
— 延迟约束（Deferred constraint）检查会在COMMIT时检查违例
对于没有日志记录的数据库
— 分离约束(Detached constraint)检查会在执行语句时检查表中处理的每行的违例，是唯一可用于没有日志记录的数据库的模式，但不适用于具有日志记录的数据库

可以使用SET CONSTRAINTS语句更改事务模式。例如：

SET CONSTRAINTS pk_orders,fk_orders DEFERRED;

您可以通过DEFERRED或IMMEDIATE子句标识SET CONSTRAINTS命令的事务模式格式。 SET CONSTRAINT语句的事务模式格式的持续时间是其执行的事务。您不能执行SET CONSTRAINTS语句来设置事务之外的事务模式。一旦执行COMMIT WORK或ROLLBACK WORK语句，事务模式将恢复为IMMEDIATE。

SET CONSTRAINTS命令也用于更改约束的对象模式。SET CONSTRAINTS命令的对象模式格式由ENABLED，DISABLED或FILTERING子句标识。

2. 即时约束（Immediate constraint）检查

CREATE TABLE test (current_no INTEGER UNIQUE);
UPDATE test SET current_no = current_no + 1;

$F749`D80YFYN{4_C6R$

F749`D80YFYN{4_C6R867×339 8.54 KB

即时（或有效）检查指定约束检查在每个语句的末尾发生。如果一些约束不满足，则似乎没有执行该语句。即时检查是默认模式。

在上述示例中，在更新第一行之后但在更新第二行之前，存在违反唯一索引约束的重复值。但是，在更新所有行之后，所有值都是唯一的，因此成功执行语句。

2.1 如何实施即时约束检查

违反约束的更改可以成功，但会记录为违例。稍后，在语句的末尾会进行检查以查看违例是否仍然存在。如果仍然存在，则会返回错误并撤销该语句。保留点用于允许数据库服务器撤销单个语句的影响，而不会撤销同一事务中早期的更改。通过在语句开头建立保留点，如果在有效检查期间发生约束冲突，数据库服务器就可以回滚到该保留点。

对于引用约束，内存缓冲区或临时表会记录违例。一个临时表记录每个引用对的违例。临时表保存违例的键值。当插入，删除和更新行时，将会更新临时表以反映新的违例并删除旧的违例。稍后，当检查完成后，将会扫描临时文件，对于那些仍然有效的键，将重新验证违例。当违例已消除时，将从临时表中删除记录。为了检查约束，将再次使用内存缓冲区或临时表。但是，这次临时表只记录违反行的rowid。当更新行时，将从临时表中删除通过检查约束的行。检查完成后，临时表应为空。

对于唯一索引，检查是逐行执行的，而不是在语句的末尾执行。如果要执行有效检查，请使用唯一约束，而不是创建唯一索引。

3. 延迟约束（Deferred constraint）检查

ALTER TABLE orders ADD CONSTRAINT PRIMARY KEY (order_num);

ALTER TABLE items ADD CONSTRAINT FOREIGN KEY (order_num) REFERENCES orders;

如果设置了延迟约束检查，那么将提交此事物；如果未设置，那么将失败。

BEGIN WORK;
SET CONSTRAINTS ALL DEFERRED;
UPDATE orders SET order_num = 1006
WHERE order_num = 1001;
UPDATE items SET order_num = 1006
WHERE order_num = 1001;
COMMIT WORK;

在上述的示例中，假定order_num列的数据类型是一个整数，而不是一个serial列。serial列是不能被更新的。当父表和子表中的主键值和外键值更改为新值时，将使用延迟检查。例如，如果要在orders和items表中更改订单编号，那么您可以使用延期检查。

当需要切换表中两行或更多行的主键值时，也会使用延迟检查。

3.1 如何实施延迟约束检查

延迟检查指定在事务提交或用户将模式更改为即时检查之前，不会发生约束检查。如果在提交时发生约束错误，则回滚事务。

在上述示例中，如果约束模式未设置为延迟，那么该语句将失败。这是因为引用约束强制所有项都必须有一个订单的规则。在第一个更新语句中会发生故障，因为存在没有订单的items（orders 表中不再存在订单号1001）。

延迟检查与即时检查类似。但是，对违例的检查是在事务结束时进行的，而不是在语句结束时。您必须在事务中设置SET CONSTRAINTS ALL DEFERRED语句。从设置到事务结束期间是有效的。您也可以使用约束名称替换关键字ALL，以便只延迟特定的约束。例如：

SET CONSTRAINTS uniq_ord DEFERRED;

延迟检查不使用唯一索引（也就是使用UNIQUE关键字创建的索引）。如果要在提交时完成检查，请使用唯一约束，而不是创建唯一索引。

4. 分离约束（Detached Constraint）检查

CREATE TABLE test (current_no INTEGER UNIQUE);
UPDATE test SET current_no = current_no + 1;

J$_VA`%IGH%8SP2~T9E

拆离检查是唯一可用于no logging数据库和使用 WITH NO LOG 创建的临时表中的模式。如果日志记录未开启，则无法执行有效检查所需的回滚。

4.1 如何实施分离约束检查

分离约束检查是逐行完成的。一旦发生约束错误，将立即向用户返回错误，并且不会执行其余的语句。

5. 性能影响

通过对主键和外键使用索引来实现引用约束。
在进行UPDATE，INSERT和DELETE操作时也更新索引。
在每个UPDATE，INSERT和DELETE操作上查找索引。
需要许多锁。所有使用的索引都具有共享锁。

通过对适当的列内部创建唯一索引来强制执行唯一约束。
当创建引用约束时，在外键列上构建非唯一索引。如果索引已经存在，则使用该索引。
列可以同时具有引用和唯一约束。也可以有两个不同的引用约束。在这些情况下，使用单个索引来强制执行多个约束。

6. 删除约束

ALTER TABLE orders DROP CONSTRAINT pk_orders;

ALTER TABLE可以删除主键约束和任何相应的外键约束。当删除外键约束时，相应的主键约束不受影响。

CREATE TABLE orders (
order_num SERIAL,
order_date DATE,
PRIMARY KEY (order_num) CONSTRAINT pk_orders);

CREATE TABLE items (
item_num SMALLINT,
order_num INTEGER,
FOREIGN KEY (order_num) REFERENCES orders
CONSTRAINT fk_orders);

ALTER TABLE orders DROP order_num;

当删除具有约束的列时，不仅仅会影响ALTER TABLE语句中提到的表。引用已删除列的任何约束也将被删除。

在上述示例中，删除表 orders 中的主键列需要在删除约束时锁定表 items 。此外，用于实现约束的索引只有在为该约束构建时才被删除。

7. 删除和更新父行

DELETE FROM orders WHERE order_num = (1004);

GU6(P(RSR%6ALOPEU4

当在父表中删除或更新行时，索引用于支持引用完整性。

在删除或更新父（主）表中的行之前，数据库服务器将查找与要更新或删除的行的主键对应的任何外键。当找到相应的外键时，在索引中的外键上放置一个共享锁。需要锁来测试要删除的键或尚未提交的新插入的外键的存在。

8.插入和更新子行

INSERT INTO items (order_num) VALUES (1004);

HGM$8M3BAMUK@Y1$4_PX

将行插入或更新到子（从）表中时使用索引来支持引用完整性。

在插入或更新行之前，数据库服务器将通过更新来查看此表上设置为非NULL值的所有外键。对于这些外键，数据库服务器使用与主键对应的唯一索引，并对父表执行查找。如果找到行，那么数据库服务器将会在索引键上放置共享锁来确保在插入或更新子行之前不删除该行。在插入或更新引用行之前，将一直保持锁定。

更多信息内容请移步星瑞格官方社区，期待大家加入
Sinoregal Tech ForumAsk questions, share solutions, and get to know the Sinoregal community.https://forum.sinoregal.cn/