DOES NOT REFUSE DIRECTLY IMMEDIATELY

Question

I read this about the SQL keyword DEFERRABLE in database systems - Complete book.

The last [NOT DEFERRABLE] by default means that every time the database modification instruction is executed, the constraint is checked immediately if the modification can violate the foreign key constraint.

However, if we declare a DEFERRABLE constraint, then we have the opportunity to wait for the transaction to complete before checking the constraint.

We execute the DEFERRABLE keyword using INITIALLY DEFERRED or INITIALLY IMMEDIATE . In the first case, the check will be delayed immediately before each transaction. In the latter case, verification will be carried out immediately after each application.

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

Answer 1

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

This is useful if you usually want to check restrictions during an instruction, but, for example, batch loading wants to delay the check until it is committed.

The syntax for deferring restrictions is different for different DBMSs.

With NOT DEFERRABLE you can never defer validation until commit.

Answer 2

In addition to the other (correct) answers, speaking of PostgreSQL , it is necessary to indicate that:

• with NOT DEFERRABLE each row is checked during insert / update

• 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

Therefore, it is wrong to say that the DEFERRABLE constraint acts like NOT DEFERRABLE when it is set to IMMEDIATE.

---

Let's clarify 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 delete the DEFERRABLE INITIALLY IMMEDIATE statement,

ERROR: the value of the duplicated key violates the unique restriction "example_row_col_key". MORE: Key ("row", column) = (2, 2) already exists. ********** Mistake **********

ERROR: the value of the duplicated key violates the unique constraint of "example_row_col_key" SQL state: 23505 Details: the key ("row", column) = (2, 2) already exists.

---

APPENDIX (October 12, 2017)

This behavior is really documented here , the Compatibility section:

In addition, PostgreSQL checks the immediate limitations of uniqueness immediately, and not at the end of the statement, as the standard suggests.

Answer 3

Besides the obviousness that it is able to put off, the difference is actually performance. If there was no penalty for execution, then there would be no need to choose the option of deferral or not - all restrictions would simply be deferred.

The decrease in performance is due to the optimization that the database can perform, given how limited the data is. For example, an index created to support a unique constraint in Oracle cannot be a unique index if the constraint is deferred because temporary resolution of duplicates must be allowed. However, if the constraint is not deferred, the index may be unique.

Answer 4

NOT DEFERRABLE - you cannot change the constraint check, oracle checks it after each statement (i.e., immediately after the insert statement).

FAILURE FAILURE WRONG - oracle checks the constraint after each statement. BUT, you can change it after each transaction (i.e. after commit):

 set constraint pk_tab1 deferred; 

Answer 5

I'm very late to the party, but I wanted to add that - as of December 2018 - only two databases that I know about (maybe more) offer some level of implementation of this standard SQL function:

 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 Although Oracle 12c accepts the NOT DEFERRABLE constraints of NOT DEFERRABLE , it actually ignores it and makes it work as DEFERRABLE INITIALLY IMMEDIATE .

As you can see, Oracle does not implement the first type ( NOT DEFERRABLE ), and therefore developers using Oracle (in this case, OP) may get confused and consider the first two types to be equivalent.

Interestingly, Oracle and PostgreSQL have different types by default. Maybe this affects performance.

Answer 6

Create table Este codigo me da el siguiente error, enter image description here