sql - 不可延期与可延期最初立即

Question

我在 Database Systems - The Complete Book 中读到了关于 SQL 关键字 DEFERRABLE 的内容。

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 DEFERRABLE by either INITIALLY DEFERRED or 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 IMMEDIATE 有何不同？在这两种情况下，似乎在每个单独的语句之后都会检查任何约束。

Answer 1

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

如果您通常想在语句时检查约束，这很有用，但是对于例如批量加载希望将检查推迟到提交时间。

不过，对于不同的 DBMS，延迟约束的语法是不同的。

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