]
07-20-2023, 10:15 AM
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.
> 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.