Deferrable Constraints

As someone who has done a lot of data manipulation work I strongly support anything that goes to improve the quality of data. Within the database that means I encourage the use of declarative constraints. In Oracle, these are:

• Primary key constraints
• Unique key constraints
• Foreign key constraints
• Check constraints
• Not Null constraints

As anyone who has dealt with databases would know, data inserted or updated must abide by whatever constraints have been declared on a table. Normally this check is made at the point the data modification is made. However, deferrable constraints offers us the ability to delay the checking to the commit point.

So why might we need deferrable constraints? Depending on the data processes it might be that data obtained early on in the process is incomplete and gets supplemented as the process progresses. Deferrable constraints allow us to gradually build up the data knowing that at the end of the transaction the data will be complete. It’s certainly a better situation than dropping the constraints completely!

Let’s look at a very simple example of a deferrable NOT NULL constraint. We’ll create a table with two columns, COL_1 and COL_2, both with NOT NULL constraints but the COL_2 constraint will be deferrable:

CREATE TABLE t1
   (col_1   NUMBER CONSTRAINT col_1_not_null NOT NULL
   ,col_2   NUMBER CONSTRAINT col_2_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED)
/

I’ve taken the liberty of naming the NOT NULL constraints in the above DDL, which is something that I would not normally do for NOT NULL constraints, as we’ll see the names in the exceptions thrown.

If we insert a row into our table with NULL for COL_1 we get:

SQL>INSERT INTO t1
  2  VALUES (NULL, 1)
  3  /
VALUES (NULL, 1)
        *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("DEVELOPER"."T1"."COL_1")

However if the COL_2 value is set to NULL:

SQL>INSERT INTO t1
  2  VALUES (1, NULL)
  3  /

1 row created.

we find that the row gets created. If we were to commit at this point then the NOT NULL constraint check on COL_2 kicks in and we get:

SQL>COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (DEVELOPER.COL_2_NOT_NULL) violated

However, if we initially insert NULL into COL_2 and then provide a value prior to the commit we find all goes through without error:

SQL>INSERT INTO t1
  2  VALUES (1, NULL)
  3  /

1 row created.

SQL>
SQL>UPDATE t1
  2  SET    COL_2 = 1
  3  WHERE  COL_1 = 1
  4  /

1 row updated.

SQL>COMMIT
  2  /

Commit complete.

You’ll note that the deferrable constraint was declared with the keywords DEFERRABLE INITIALLY DEFERRED. The DEFERRABLE keyword notes that the constraint is capable of being deferred while the INTIALLY DEFERRED part specifies that the constraint defaults to deferred checking. The alternative to this is DEFERRABLE INITIALLY IMMEDIATE, which means the constraint check is not deferred by default but can be made deferrable.

To make all deferrable constraint deferred issue the follow:

ALTER SESSION SET CONSTRAINTS = DEFERRED

and to reverse this, i.e. turn off deferred checking:

ALTER SESSION SET CONSTRAINTS = IMMEDIATE

Use:

ALTER SESSION SET CONSTRAINTS = DEFAULT

puts deferrable constraints into DEFERRED or IMMEDIATE according to how they are declared. A session can change its constraint setting in the middle of a transaction without impacting the DML that has occurred prior the change.

Note, the above commands only impact constraints that are declare to be DEFERRABLE. By default constraints are NOT DEFERRABLE, which means they can never be deferred. The only way to enable deferrable checking on a constraint that has been declared as not deferrable is to drop it and recreate it with one of the deferrable options (at least, as of Oracle 12.1.0.2. I guess future versions may support this).

If you have read my earlier post on unique constraints indexes then you’ll know that by default Oracle will create a unique index to enforce the constraint. If the unique constraint is declared as DEFERRABLE then the index Oracle creates will not be unique. On consideration this makes senses. During the course of a transaction Oracle might have duplicate entries on the unique column(s) and a unique index would prevent this scenario.

Advertisement