sql - NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE

Question

I read this about the SQL keyword DEFERRABLE in Database Systems - The Complete Book.

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.

How is NOT DEFERRABLE different from DEFERRABLE INITIALLY IMMEDIATE? In both cases, it seems, any constraints are checked after each individual statement.

Answer 1

With DEFERRABLE INITIALLY IMMEDIATE you can defer the constraints on demand when you need it.

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.

With NOT DEFERRABLE you will never ever be able to defer the checking until commit time.