oracle sql defer VS disable

Question

In oracle sql, setting a constraint to deferred VS disabling a constraint seem to have similar functionalities. Are there any significant differences between the two? It would be much appreciated if you can illustrate their uses with real world scenarios.

thanks

Answer 1

A disabled constraint is one that's defined in the database but not enforced at all. (Like dropping a constraint only it's easier to switch back on.) As for deferrable:

http://docs.oracle.com/cd/E18283_01/server.112/e16508/glossary.htm#CHDIBAAA

deferrable constraint

A constraint that permits a SET CONSTRAINT statement to defer constraint checking until after the transaction is committed. A deferrable constraint enables you to disable the constraint temporarily while making changes that might violate the constraint.

It's worth noting:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm

You cannot alter the deferrability of a constraint. Whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it.