Good morning,
I'm reading the Oracle Concepts document (you might already know that :) )
Anyway... on page 5-11,on deferrable constraints, one passage states:
You can set the default behavior for when the database checks the deferrable
constraint. You can specify either of the following attributes:
■ INITIALLY IMMEDIATE
The database checks the constraint immediately after each statement executes. If
the constraint is violated, then the database rolls back the statement.
■ INITIALLY DEFERRED
The database checks the constraint when a COMMIT is issued. If the constraint is
violated, then the database rolls back the transaction.
This is crystal clear and exactly as I thought it would be... then comes this example that seems to contradict the above statement:
<tt>
Assume that a deferrable NOT NULL constraint on employees.last_name is set to
INITIALLY DEFERRED. A user creates a transaction with 100 INSERT statements,
some of which have null values for last_name. When the user attempts to commit,
the database rolls back all 100 statements.
However, if this constraint were INITIALLY
IMMEDIATE, then the database would not roll back the transaction.
</tt>
The portion that is underlined seems to imply that if the constraint were INITIALLY IMMEDIATE, the 100 insert statements would succeed in setting some of the last_name columns to NULL (since there would be no transaction rollback).
The question is (or more appropriately, I'd like to have confirmation that:) even if the constraint was set to INITIALLY IMMEDIATE the insert statements that would result in the last_name being set to null would fail and, that not only they would fail but, they would fail as each insert is executed not deferred until a COMMIT is attempted. Is this correct ?
Thank you for your help,
John.
PS: I tried to quote the second passage but then it would not underline the sentence I wanted to attract attention to. Is there a way to underline quoted text ? if so how ? thank you again.