SQL 不可延期与最初立即可延期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5300307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE
提问by Pieter
I read this about the SQL keyword DEFERRABLEin Database Systems - The Complete Book.
我DEFERRABLE在Database Systems - The Complete Book 中阅读了有关 SQL 关键字的内容。
The latter [NOT DEFERRABLE]is default, and means that every time a database modification statement is executed, the constraint is checked immediately afterwards, if the modification could violate the foreign-key constraint.
However, if we declare a constraint to be DEFERRABLE, then we have the option of having it wait until a transaction is complete before checking the constraint.
We follow the keyword DEFERRABLEby either INITIALLY DEFERREDor INITIALLY IMMEDIATE. In the former case, checking will be deferred to just before each transaction commits. In the latter case, the check will be made immediately after each statement.
后者[NOT DEFERRABLE]是默认值，意味着每次执行数据库修改语句时，如果修改可能违反外键约束，则会立即检查约束。
但是，如果我们将约束声明为DEFERRABLE，那么我们可以选择让它等到事务完成后再检查约束。
我们通过INITIALLY DEFERRED或INITIALLY IMMEDIATE跟随关键字DEFERRABLE。在前一种情况下，检查将推迟到每个事务提交之前。在后一种情况下，将在每个语句之后立即进行检查。
How is NOT DEFERRABLEdifferent from DEFERRABLE INITIALLY IMMEDIATE? In both cases, it seems, any constraints are checked after each individual statement.
如何NOT DEFERRABLE不同DEFERRABLE INITIALLY IMMEDIATE？在这两种情况下，似乎在每个单独的语句之后检查任何约束。
采纳答案by a_horse_with_no_name
With DEFERRABLE INITIALLY IMMEDIATEyou can defer the constraints on demand when you need it.
有了它，DEFERRABLE INITIALLY IMMEDIATE您可以在需要时推迟按需限制。
This is useful if you normally want to check the constraints at statement time, but for e.g. a batch load want to defer the checking until commit time.
如果您通常希望在语句时检查约束，这很有用，但例如，批量加载希望将检查推迟到提交时间。
The syntax how to defer the constraints is different for the various DBMS though.
但是，对于各种 DBMS，如何推迟约束的语法是不同的。
With NOT DEFERRABLEyou will never ever be able to defer the checking until commit time.
有了它，NOT DEFERRABLE您将永远无法将检查推迟到提交时间。
回答by Teejay
Aside from the other (correct) answers, when speaking of PostgreSQL, it must be stated that:
除了其他（正确）答案之外，在谈到 PostgreSQL 时，必须指出：
with NOT DEFERRABLEeach row is checked at insert/update time
with DEFERRABLE(currently IMMEDIATE) all rows are checked at the end of the insert/update
with DEFERRABLE(currently DEFERRED) all rows are checked at the end of the transaction
使用NOT DEFERRABLE在插入/更新时检查每一行
使用DEFERRABLE（当前为IMMEDIATE）在插入/更新结束时检查所有行
使用DEFERRABLE（当前为DEFERRED）在事务结束时检查所有行
So it's not correctto say that a DEFERRABLE constraint acts like a NOT DEFERRABLE one when it is set to IMMEDIATE.
因此，当 DEFERRABLE 约束设置为 IMMEDIATE 时，说它就像一个 NOT DEFERRABLE 约束是不正确的。
Let's elaborate on this difference:
让我们详细说明这种差异：
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;
This correctly outputs:
这正确输出：
But if we remove the DEFERRABLE INITIALLY IMMEDIATE instruction,
但是如果我们删除 DEFERRABLE INITIALLY IMMEDIATE 指令，
ERROR: duplicate key value violates unique constraint "example_row_col_key" DETAIL: Key ("row", col)=(2, 2) already exists. ********** Error **********
ERROR: duplicate key value violates unique constraint "example_row_col_key" SQL state: 23505 Detail: Key ("row", col)=(2, 2) already exists.
错误：重复键值违反唯一约束“example_row_col_key”详细信息：键（“行”，列）=（2, 2）已经存在。********** 错误 **********
错误：重复键值违反唯一约束“example_row_col_key” SQL 状态：23505 详细信息：键（“行”，列）=（2, 2）已经存在。
ADDENDUM(October 12, 2017)
附录（2017 年 10 月 12 日）
This behavior is indeed documented here, section "Compatibility":
这种行为确实记录在此处，“兼容性”部分：
Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.
此外，PostgreSQL 会立即检查不可延迟的唯一性约束，而不是像标准建议的那样在语句结束时检查。
回答by Ryan
Aside from the obvious of being able to defer, the difference is actually performance. If there wasn't a performance penalty then there would be no need to have an option to choose deferrable or not -- all constraints would simply be deferrable.
除了明显的能够延迟之外，差异实际上是性能。如果没有性能损失，那么就没有必要选择可延迟与否——所有约束都只是可延迟的。
The performance penalty has to do with optimizations that the database can perform given the knowledge of how the data is restricted. For example, the index that is created to back a unique constraint in Oracle cannot be a unique index if the constraint is deferrable since temporarily allowing duplicates must be allowed. However, if the constraint is not deferrable then the index can be unique.
性能损失与数据库在知道数据如何限制的情况下可以执行的优化有关。例如，如果约束是可延迟的，则为支持 Oracle 中的唯一约束而创建的索引不能是唯一索引，因为必须允许临时允许重复。但是，如果约束不可延迟，则索引可以是唯一的。
回答by The Impaler
I'm very late to the party but I wanted to add that -- as of December 2018 -- only two databases I know of (there may be more) offer some level of implementation of this standard SQL feature:
我参加聚会已经很晚了，但我想补充一点——截至 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 Even though Oracle 12c accepts the NOT DEFERRABLEconstraint state, it actually ignores it and makes it work as DEFERRABLE INITIALLY IMMEDIATE.
*1 尽管 Oracle 12c 接受NOT DEFERRABLE约束状态，但它实际上会忽略它并使其像DEFERRABLE INITIALLY IMMEDIATE.
As you see, Oracle does not implement the first type (NOT DEFERRABLE), and that's why developers using Oracle (the OP in this case) may get confused and consider the first two types equivalent.
如您所见，Oracle 没有实现第一种类型 ( NOT DEFERRABLE)，这就是使用 Oracle（在本例中为 OP）的开发人员可能会感到困惑并认为前两种类型等效的原因。
Interestingly enough Oracle and PostgreSQL have a different default type. Maybe it has performance implications.
有趣的是，Oracle 和 PostgreSQL 有不同的默认类型。也许它有性能影响。
回答by hajili
NOT DEFERRABLE - you cannot change the constraint checking, oracle checks it after each statement(i.e. directly after insert statement).
NOT DEFERRABLE - 您不能更改约束检查，oracle 在每个语句之后（即直接在插入语句之后）对其进行检查。
DEFERRABLE INITIALLY IMMEDIATE - oracle checks constraint after each statement. BUT, you can change it to after each transaction(i.e. after commit):
DEFERRABLE INITIALLY IMMEDIATE - oracle 在每个语句之后检查约束。但是，您可以在每个事务之后（即提交之后）将其更改为：
set constraint pk_tab1 deferred;
