约束的DEFERRABLE and DEFERRED特性

看到经常有人问这个东西，doc看到了，随手记录一下。

[@more@]

DEFERRABLE and DEFERRED
You can temporarily set a constraint to DEFERRED so that a large set of data might be
processed without any constraint checking until after the transaction is completed. To
do this, create the constraint with the DEFERRABLE clause, as follows:
ALTER TABLE SHIPS DROP CONSTRAINT FK_SHIPS_PORTS;
ALTER TABLE SHIPS ADD CONSTRAINT FK_SHIPS_PORTS FOREIGN KEY (HOME_PORT_ID)
REFERENCES PORTS (PORT_ID) DEFERRABLE;
The default setting for constraints is NOT DEFERRABLE.
Once the constraint has been created as DEFERRABLE, you may issue this
statement during a session:
SET CONSTRAINT FK_SHIPS_PORTS DEFERRED;
Alternatively, you can set all constraints to DEFERRED:
SET CONSTRAINT ALL DEFERRED;
Once the SET CONSTRAINT command establishes a given constraint—or all
constraints—as DEFERRED, the current transaction will temporarily ignore the
deferred constraints, and accept rows of data that might violate those constraints.
However, once a commit event occurs, the constraint automatically changes state
from DEFERRED to IMMEDIATE, and the constraints will be applied. If any are
violated, the commit will fail and all data is rolled back.
At any time, you can restore the default behavior of constraints with this command:
SET CONSTRAINT FK_SHIPS_PORTS IMMEDIATE;
Alternatively, you can set all constraints to IMMEDIATE:
SET CONSTRAINT ALL IMMEDIATE;
Once set to IMMEDIATE, constraints go back to their default behavior, and are
checked after each DML statement is executed.
This approach is somewhat similar to disabling commands, only it’s a more
temporary state.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1055715/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1055715/