Deferred Integrity ConstraintsDeferred integrity constraints are those that are not enforced until the user attempts to commit the transaction.
At the time of the commit, if any data entered during the transaction violates an integrity constraint, Oracle will then rollback the entire transaction and signal an error to the user.
Deferred integrity constraints should only be used when absolutely necessary. If at all possible, avoid hard-coding constraint names with your application code.
They should only be used when solving a design issue. Be aware that very few applications support commits time errors.
Not all constraints in the database can be set to deferrable. Only when the constraint is created with the "deferrable" option can user processes set the constraint as deferrable.
If the integrity constraint is created with the "not deferrable" option, then user processes will always be bound by immediate integrity constraint. Along with defining whether a constraint is deferrable or not, you can also set its default behavior with the initially deferred and initially immediate statement.
For example, we can define the constraints as:
CREATE TABLE emp (
SET CONSTRAINT emp_no_fk_par DEFERRED;
1 row updated.
A constraint may be defined as deferrable, which means you can switch it between deferred and immediate.
If a constraint is in the deferred state, you can modify data in a way that fails the constraint test.
However, a constraint can only be deferred for the duration of a transaction. As soon as the transaction commits, the constraint becomes immediate.
You also have the option of explicitly setting the constraint back to immediate before the end of the transaction. At this point, your modifications will be tested against the constraint, and you will not be allowed to commit a transaction that leaves the data in an illegal state.
The initially deferred option will enable the constraint as deferrable by default whereas the initially immediate option requires the user process to explicitly set the transaction or session to deferrable.
The initially deferred and not deferrable keywords are mutually exclusivE.
Why use deferred integrity constraints?
Creating deferred integrity constraints:
Apart from that, there are new columns in the "user_constraints /dba_constraints /all_constraints" views that will give you information on whether the constraint is deferrable. For example:
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.