Manage Learn to apply best practices and optimize your operations.

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.


 

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close