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.
