Karl about the Oracle Database

Hi reader,

do you know how deferred constraints work? No problem : The constraint is not checked after statement completion but on COMMIT of the transaction. Simple i thought. But a deferred constraint could have effect on your code. I created a package for the handling of CUSTOMER structures. A root node is always needed if a CUSTOMER of a preferred pool is created or updated. A CUSTOMER may only appear once in the structure and due to application logic the Unique key on the CUSTOMER is checked deferred.

– Create/Recreate primary, unique and foreign key constraints
alter table CUSTOMERSTRUCTS
  add constraint CUSTOMERSTRUCTS_UK01 unique (CUSTOMERID) INITIALLY DEFERRED DEFERRABLE
  using index
  tablespace CUSTIXM
;

The procedure simple ignores the UNIQUE key vailation due to an insert of a companies root node if it already exists :

PROCEDURE Create_Root_Node(p_CUSTOMER IN Guid) IS
  BEGIN
 
    INSERT INTO CUSTOMERstructs
      (Id,
       CUSTOMERid,
       PARENT,
       Lastupdate)
    VALUES
      (Sys_Guid(),
       p_CUSTOMER,
       NULL,
       SYSDATE);
 
  EXCEPTION
    WHEN Dup_Val_On_Index THEN
      NULL; – do nothing
    WHEN OTHERS THEN
      Eventlogmgr.Logapperror(SQLCODE,
                              ‘Error creating CUSTOMERstructs Root Node’,
                              ‘CUSTOMERstructs_Util’,
                              ‘Create_Root_Node’,
                              ‘INSERT INTO CUSTOMERSTRUCTS’);
   
      RAISE;
  END Create_Root_Node;

Now the test :

SQL>
SQL> DECLARE
  2    l_CUSTOMER_Id CUSTOMER.Id%TYPE;
  3  BEGIN
  4 
  5    SELECT Id
  6      INTO l_CUSTOMER_Id
  7      FROM CUSTOMER
  8 
  9     WHERE Rownum < 2
 10       AND Isis_Prefered = 1;
 11 
 12    — Ignore already inserted CUSTOMER node.
 13    CUSTOMERstructs_Util.Create_Root_Node(p_CUSTOMER => l_CUSTOMER_Id);
 14    CUSTOMERstructs_Util.Create_Root_Node(p_CUSTOMER => l_CUSTOMER_Id);
 15    CUSTOMERstructs_Util.Create_Root_Node(p_CUSTOMER => l_CUSTOMER_Id);
 16 
 17  END;
 18  /

PL/SQL procedure successfully completed

SQL>

Seems to work! Finally the commit :

SQL> COMMIT;

COMMIT

ORA-02091: Transaktion wurde zurückgesetzt
ORA-00001: Unique Constraint (ISIS.CUSTOMERSTRUCTS_UK01) verletzt

SQL>

Uups! What happened here? The part of the Code …

EXCEPTION
    WHEN Dup_Val_On_Index THEN
      NULL;– do nothing

… was never executed because of the deferred constraint. On the time of Commit the UNIQUE constraint was hurt. Following Code worked properly with own implemented unique key lookup:

PROCEDURE Create_Root_Node(p_CUSTOMER IN Guid) IS
  l_Num INTEGER;
BEGIN
  – manual lookup because of deferred unique constraint
  SELECT 1
    INTO l_Num
    FROM CUSTOMERstructs Cs
   WHERE Cs.CUSTOMERid = p_CUSTOMER;
EXCEPTION
  WHEN No_Data_Found THEN
    BEGIN
      INSERT INTO CUSTOMERstructs
        (Id,
         CUSTOMERid,
         PARENT,
         Lastupdate)
      VALUES
        (Sys_Guid(),
         p_CUSTOMER,
         NULL,
         SYSDATE);
    EXCEPTION
      WHEN OTHERS THEN
        Eventlogmgr.Logapperror(SQLCODE,
                                ‘Error creating CUSTOMERstructs Root Node’,
                                ‘CUSTOMERstructs_Util’,
                                ‘Create_Root_Node’,
                                ‘INSERT INTO CUSTOMERstructs’);
        RAISE;
    END;
END Create_Root_Node;

and it did …

SQL>
SQL> DECLARE
  2    l_CUSTOMER_Id CUSTOMER.Id%TYPE;
  3  BEGIN
  4 
  5    SELECT Id
  6      INTO l_CUSTOMER_Id
  7      FROM CUSTOMER
  8 
  9     WHERE Rownum < 2
 10       AND Isis_Prefered = 1;
 11 
 12    — Ignore already inserted CUSTOMER node.
 13    CUSTOMERstructs_Util.Create_Root_Node(p_CUSTOMER => l_CUSTOMER_Id);
 14    CUSTOMERstructs_Util.Create_Root_Node(p_CUSTOMER => l_CUSTOMER_Id);
 15    CUSTOMERstructs_Util.Create_Root_Node(p_CUSTOMER => l_CUSTOMER_Id);
 16 
 17  END;
 18  /

PL/SQL procedure successfully completed

SQL> COMMIT;

Commit complete

Conclusion

changing a Constraint to deferred could bypass some code fragments in your application which rely on immediate check of the constraints. Having this in mind deferred constraints are a comfortable way to change dependent data.

HTH

Karl