How to work with deferred constraints
Here's how to defer a unique key constraint until the end of the transaction.
A colleague of mine recently asked how he could defer a unique key constraint until the end of his transaction, so that he could temporarily have two key values the same, which he would then correct before concluding the transaction. I suggested making the key constraint deferrable. He then asked how to redefine the constraint as deferred in an UPDATE statement? The correct method is a bit more involved, so I responded with this example:
First you'll have to drop the constraint and recreate it with the DEFERRABLE clause. You can also specify whether this deferrable state is on or off (INITIALLY IMMEDIATE/DEFERRED). Then you can use the SET CONSTRAINT command to control this feature. For example, you may want only one particular application module to have the ability to perform a deferred transaction, and leave the constraint active otherwise, e.g.:
CREATE TABLE blick (num NUMBER, str VARCHAR2(1)); Table created. ALTER TABLE blick ADD CONSTRAINT pk_blick PRIMARY KEY (num) DEFERRABLE INITIALLY IMMEDIATE; Table altered. INSERT INTO blick (num, str) VALUES (1, 'A'); 1 row created. INSERT INTO blick (num, str) VALUES (1, 'B'); ORA-00001: unique constraint (PPDEV.PK_BLICK) violated SET CONSTRAINT pk_blick DEFERRED; Constraint set. INSERT INTO blick (num, str) VALUES (1, 'B'); 1 row created. UPDATE blick SET num=2 WHERE str='B'; 1 row updated. -- validate the new rows pass the constraint SET CONSTRAINT pk_blick IMMEDIATE; Constraint set. COMMIT; Commit complete.
The DDL to change between deferred and immediate will have to be done using Execute Immediate:
BEGIN EXECUTE IMMEDIATE 'SET CONSTRAINT pk_blick DEFERRED'; INSERT INTO blick (num, str) VALUES (3, 'C'); INSERT INTO blick (num, str) VALUES (3, 'D'); UPDATE blick SET num=4 WHERE str='D'; EXECUTE IMMEDIATE 'SET CONSTRAINT pk_blick IMMEDIATE'; COMMIT; END; /
Notice I resume constraint checking BEFORE issuing the COMMIT. If the constraint will be violated by my transaction, I want to know it before committing the changes.
For More Information
- For more info: More info about deferred contstraints from Oracle
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.