不可推迟与可推迟的初始即时对比

我在 数据库系统-全书中读到了关于 SQL 关键字 DEFERRABLE的内容。

后一个 [NOT DEFERRABLE]是默认值，这意味着每次执行数据库修改语句时，如果修改可能违反外键约束，则会立即检查约束。

但是，如果我们将约束声明为 延期，那么我们可以选择让它等到事务完成后再检查约束。

我们通过 INITIALLY DEFERRED或 一开始很快跟随关键字 延期。在前一种情况下，检查将推迟到每个事务提交之前。在后一种情况下，检查将在每个语句之后立即进行。

NOT DEFERRABLE和 DEFERRABLE INITIALLY IMMEDIATE有什么不同? 在这两种情况下，似乎在每个单独的语句之后都会检查任何约束。

116963 次浏览
小开
最佳答案

使用 DEFERRABLE INITIALLY IMMEDIATE，您可以在需要时延迟约束。

如果您通常希望在语句时检查约束，那么这非常有用，但是对于批量加载来说，它希望将检查推迟到提交时。

但是，对于不同的 DBMS，如何延迟约束的语法是不同的。

使用 NOT DEFERRABLE，您将永远不能将检查推迟到提交时间。

小开

除了显而易见的可以推迟之外，不同之处实际上在于性能。如果不存在性能损失，那么就没有必要选择延迟还是不延迟——所有的约束都将是可延迟的。

性能损失与数据库在知道如何限制数据的情况下可以执行的优化有关。例如，在 Oracle 中为支持唯一约束而创建的索引，如果约束是可延迟的，则不能是唯一索引，因为必须允许临时允许重复。但是，如果约束不可延迟，则索引可以是唯一的。

小开

NOT DEFERABLE-不能更改约束检查，Oracle 在每个语句后检查(即直接在插入语句后)。

DEFERBERINITIALYIMMEDIATE-oracle 在每个语句之后检查约束。但是，您可以在每个事务之后(即提交之后)将其更改为:

set constraint pk_tab1 deferred;
小开

除了其他(正确的)答案 说到 PostgreSQL 时 < strong > 外，还必须说明:

  • 在 不能延期中，每行在插入/更新时被检查

  • 使用 延期(当前为 马上) ，在插入/更新结束时检查所有行

  • 使用 延期(当前为 推迟) ，在事务结束时检查所有行

因此，it's not correct表示将 DEFERable 约束设置为 IMMEDIATE 的行为类似于 NOT DEFERABLE 约束。

让我们来详细说明一下这种差异:

CREATE TABLE example(
row integer NOT NULL,
col integer NOT NULL,
UNIQUE (row, col) DEFERRABLE INITIALLY IMMEDIATE
);


INSERT INTO example (row, col) VALUES (1,1),(2,2),(3,3);


UPDATE example SET row = row + 1, col = col + 1;


SELECT * FROM example;

正确输出:

output

但是，如果我们删除了 Deferable INITIALLY IMDIATE 指令,

错误: 重复的键值违反了唯一约束 DETAIL: Key (“ row”，col) = (2,2)已经存在。 错误

错误: 重复的键值违反了唯一约束 SQL 状态: 23505详细信息: Key (“ row”，col) = (2,2) 已经存在了。

附录 < em > (2017年10月12日)

这种行为的确被记录在 给你的“兼容性”章节中:

此外，PostgreSQL 会立即检查不可延迟的唯一性约束，而不是像标准建议的那样在语句结束时检查。

小开

我来晚了，但我想补充一点——截至2018年12月——据我所知，只有两个数据库(可能还有更多)提供了这个标准 SQL 特性的一些实现:

Database    NOT DEFERRABLE  DEFERRABLE           DEFERRABLE
INITIALLY IMMEDIATE  INITIALLY DEFERRED
----------  --------------  -------------------  ------------------
Oracle      N/A*1           Yes (default)        Yes
PostgreSQL  Yes (default)   Yes                  Yes
DB2         -               -                    -
SQL Server  -               -                    -
MySQL       -               -                    -
MariaDB     -               -                    -
SAP Sybase  -               -                    -
HyperSQL    -               -                    -
H2          -               -                    -
Derby       -               -                    -

* 1尽管 Oracle 12c 接受 NOT DEFERABLE 约束状态，但它实际上忽略了它，并使其作为 DEFERABLE INITALLY IMMEDIATE 工作。

正如您所看到的，Oracle 没有实现第一种类型(NOT DEFERABLE) ，这就是为什么使用 Oracle 的开发人员(在本例中是 OP)可能会感到困惑，认为前两种类型是等价的。

Interestingly enough Oracle and PostgreSQL have a different default type. Maybe it has performance implications.


提交答案