It is interesting to note exactly when integrity constraints are checked. By default, integrity constraints are checked after the entire SQL statement has been processed.
There are also deferrable constraints that permit the validation of integrity constraints to be postponed until either the application requests they be validated by issuing a SET CONSTRAINTS ALL IMMEDIATE command or upon issuing a COMMIT.
IMMEDIATE Constraints
For the first part of this discussion, we’ll assume that constraints are in IMMEDIATE mode, which is the norm. In this case, the integrity constraints are checked immediately after the entire SQL statement has been processed.
Note that I used the term “SQL statement,” not just “statement.” If I have many SQL statements in a PL/SQL stored procedure, each SQL statement will have its integrity constraints validated immediately after its individual execution, not after the stored procedure completes.
So, why are constraints validated after the SQL statement executes? Why not during? This is because it is very natural for a single statement to make individual rows in a table momentarily inconsistent.
Taking a look at the partial work by a statement would result in Oracle rejecting the results, even if the end result would be OK.
For example, suppose we have a table like this:
$ sqlplus eoda/foo@PDB1
SQL> create table t ( x int unique );Table created.
SQL> insert into t values ( 1 );1 row created.
SQL> insert into t values ( 2 );1 row created.
SQL> commit;Commit complete.
And we want to execute a multiple-row UPDATE:
SQL> update t set x=x-1;2 rows updated.
If Oracle checked the constraint after each row was updated, on any given day we would stand a 50-50 chance of having the UPDATE fail.
The rows in T are accessed in some order, and if Oracle updated the X=1 row first, we would momentarily have a duplicate value for X, and it would reject the UPDATE.
Since Oracle waits patiently until the end of the statement, the statement succeeds because by the time it is done, there are no duplicates.