Deferrable constraint in Oracle database - be aware of them!

Recently I bumped into an error during Oracle APEX application import and I started digging in the depths of the APEX trying to figure out what the problem was. The error message stated that one foreign key constraint failed and I hoped to find a procedure with DML statement, where this error happened.

The error back trace was pretty long... and it ended at the COMMIT statement 😐At the COMMIT statement? And not at the very DML operation? 😮 I was puzzled... staring blankly at the screen... checking three times if I traced the error correctly... but I did. 😵

And suddenly I remembered that there is a parameter related to table constraints, the parameter I never used in my whole life, which determines if the constraint is validated at the moment of DML statement execution OR later on during the transaction ending COMMIT statement. This parameter is called "deferrable". And indeed, it was a case here. In the problematic APEX table all constraints were initially deferrable.

So, what is a problem with initially deferrable constraints? I mean, they work flawlessly and will prevent invalid data to be entered in the table...

BUT when You execute the PL/SQL code and some error occurs, deferrable constraints can cause the PL/SQL error back trace to be a really misleading.

The error back trace is showing that the COMMIT statement fails and You actually have no clue, which exact DML statement caused an error.

Let's look at the following example.

I'm creating a table with 2 deferrable constrains:

CREATE TABLE testing_constraints
(
  id NUMBER(10) NOT NULL primary key,
  name VARCHAR2(100),
  limit NUMBER NOT NULL,
 CONSTRAINT testing_constraints_name_nnl CHECK (name is not null) initially deferred deferrable,
  CONSTRAINT testing_constraints_limit_c CHECK (limit <= 3000) initially deferred deferrable

);

The I execute the following script, which is inserting records in the table. 

Some of the records have invalid values - either name is missing or limit is too high:

BEGIN

    INSERT INTO testing_constraints VALUES (1, 'zoran1', 0); --correct

    INSERT INTO testing_constraints VALUES (2, 'zoran2', 5000);  --not correct - limit too high

    INSERT INTO testing_constraints VALUES (3, 'zoran3', 2000);  --correct

    INSERT INTO testing_constraints VALUES (4, null, 100);  --not correct - name is mandatory

    INSERT INTO testing_constraints VALUES (5, null, 7000);  --not correct - both name and limit

    INSERT INTO testing_constraints VALUES (6, 'zoran6', 800); --correct

    INSERT INTO testing_constraints VALUES (7, 'zoran7', 200); --correct

    

    COMMIT;  --this is a line 10

END;

After the execution I got an error:

ORA-02091: transaction rolled back

ORA-02290: check constraint (ZORAN.TESTING_CONSTRAINTS_NAME_NNL) violated

ORA-06512: at line 10

Line 10 is the COMMIT statement. I have no clue, which line of code actually failed.

From the error description and visual inspection of the code it could be either line 5 or 6, where the name is null.

But imagine You have few thousands lines of code... and the only pointer to the error is leading to the COMMIT statement.

And there is another problem. Line 2 also contains invalid values but it was not mentioned in the error back trace.

So, what can You do to make constraints not deferrable and trace errors correctly?

Well, Oracle database provides a statement to make ALL or only certain constraints for the current transaction immediate and not deferrable. It goes for a statement

SET CONSTRAINTS ALL IMMEDIATE;

If I execute this statement first and then I execute the example from above, the error trace is pinpointing the first statement with the error, which is line 2 (limit too high):

[Error] Execution (23: 1): ORA-02290: check constraint (ZORAN.TESTING_CONSTRAINTS_LIMIT_C) violated
ORA-06512: at line 3

This SET CONSTRAINTS statement is not DDL statement and it is not changing tables structure.

More about this statement can be read in the official Oracle documentation:

Oracle Documentation