Menu
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.
537
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement, which is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.
Definition of nondeferrable : not able or eligible to be deferred : not deferrable nondeferrable payments/costs.
The INITIALLY DEFERRED keyword causes constraint validation to defer until commit, unless immediate is secifically requested. The following code creates two tables with a deferred constraint.
When you add a DEFERRABLE constraint, you can mark it as INITIALLY IMMEDIATE or INITIALLY DEFERRED. INITIALLY IMMEDIATE means that the constraint is checked whenever you add, update, or delete rows from a table. INITIALLY DEFERRED means that the constraint is only checked when a transaction is committed.
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.
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
