Skip to Main Content
  • Questions
  • Oracle bug in checking deferred referential integrity constraint

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Juan Carlos.

Asked: May 31, 2022 - 3:53 pm UTC

Last updated: June 06, 2022 - 1:43 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

We are working with Oracle 12c 12.1.0.2.0, and we have found an error when Oracle checks deferred referential integrity constraint. We show the error from scratch with an example:

Create this two tables:

CREATE TABLE "DEPARTMENT"
( "DEP#" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE),
CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("DEP#")
);


CREATE TABLE "EMPLOYEE"
( "EMP#" NUMBER(6,0) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE),
"DEP#" VARCHAR2(20 BYTE),
CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMP#"),
CONSTRAINT "EMPLOYEE_FK1" FOREIGN KEY ("DEP#")
REFERENCES "DEPARTMENT" ("DEP#") DEFERRABLE ENABLE
);


and create an non-unique index on foreign key DEP# on EMPLOYEE:

CREATE INDEX "EMPLOYEE_INDEX1" ON "EMPLOYEE" ("DEP#");


Insert this rows:

INSERT INTO DEPARTMENT VALUES('ACC','Accounting');
INSERT INTO DEPARTMENT VALUES('SAL','Sales');

INSERT INTO EMPLOYEE VALUES(1,'John','SAL');
INSERT INTO EMPLOYEE VALUES(2,'Mary','SAL');
INSERT INTO EMPLOYEE VALUES(3,'Peter','ACC');
COMMIT;



Now, execute the following transaction (that we now it is not reasonable):

SET CONSTRAINT EMPLOYEE_FK1 DEFERRED;
UPDATE DEPARTMENT SET DEP#='SALES' WHERE DEP#='SAL';
UPDATE EMPLOYEE SET dep#='SALES' WHERE emp#=1;
COMMIT;


Oracle responds

Constraint EMPLOYEE_FK1 correct.
1 row updated.
1 row updated.
Commit terminated.


and, at this point, the two tables contains the following rows:

DEPARTMENT

DEP#          NAME                                                 
------------- ------------------------------------------------------
ACC           Accounting                                              
SALES         Sales                                                


EMPLOYEE

EMP#       NAME                          DEP#         
---------- ----------------------------- --------------------
1          John                          SALES        
2          Mary                          SAL         
3          Peter                         ACC  


As you can see, the employee "Mary" has a department that does not exists in the table DEPARTMENT.

Interestingly, if the no-unique index on EMPLOYEE(DEP#) is not created or if the update operations in the transaction are executed in the other order (first update EMPLOYEE, then update DEPARTMENT), Oracle checks correctly the inconsistency and abort de transaction.

and Chris said...

I've not got a 12.1 database handy, but I've not been able to reproduce this on 11.2.0.4, 12.2.0.1 or 19.11:

CREATE TABLE "DEPARTMENT"
( "DEP#" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE),
CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("DEP#")
);

CREATE TABLE "EMPLOYEE"
( "EMP#" NUMBER(6,0) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE),
"DEP#" VARCHAR2(20 BYTE),
CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMP#"),
CONSTRAINT "EMPLOYEE_FK1" FOREIGN KEY ("DEP#")
REFERENCES "DEPARTMENT" ("DEP#") DEFERRABLE ENABLE
);
INSERT INTO DEPARTMENT VALUES('ACC','Accounting');
INSERT INTO DEPARTMENT VALUES('SAL','Sales');

INSERT INTO EMPLOYEE VALUES(1,'John','SAL');
INSERT INTO EMPLOYEE VALUES(2,'Mary','SAL');
INSERT INTO EMPLOYEE VALUES(3,'Peter','ACC');
COMMIT;


SET CONSTRAINT EMPLOYEE_FK1 DEFERRED;
UPDATE DEPARTMENT SET DEP#='SALES' WHERE DEP#='SAL';
UPDATE EMPLOYEE SET dep#='SALES' WHERE emp#=1;
COMMIT;

ORA-02091: transaction rolled back
ORA-02292: integrity constraint (CHRIS.EMPLOYEE_FK1) violated - child record found

select * from DEPARTMENT;

DEP#                 NAME                
-------------------- --------------------
ACC                  Accounting          
SAL                  Sales    

select * from EMPLOYEE;

      EMP# NAME                 DEP#                
---------- -------------------- --------------------
         1 John                 SAL                 
         2 Mary                 SAL                 
         3 Peter                ACC    


So speak with support to see if there's a patch available.

That said, at this point you really should be upgrading to 19c. So if this is an issue for you I suggest prioritizing upgrading.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

You forgot to create the index

Juan C. Casamayor, June 01, 2022 - 8:05 pm UTC

Thanks for your comment. I think that you forgot to create the index on EMPLOYEE(DEP#) and this is the reason you was not able to reproduce the error. I have tested the problem on Oracle 19 and I have obtained the same result
Chris Saxon
June 06, 2022 - 1:43 pm UTC

Ooops! My mistake, sorry about that.

I see the same outcome as you with the index in place.

Speak with support.

I can raise this for you, but bugs raised by customers typically carry more weight. If you want a backport for this to 12.1 you'll certainly need to speak with support; this release is pretty much at the end of its support life.

able to reproduce in 21c XE and 19c (19.14) as well.

Rajeshwaran Jeyabal, June 02, 2022 - 6:00 am UTC

Yes, with Index in place, able to see this error reproduced in 21c XE and 19c (19.14) as well.
demo@XEPDB1> CREATE TABLE "DEPARTMENT"
  2  ( "DEP#" VARCHAR2(20 BYTE) NOT NULL ENABLE,
  3  "NAME" VARCHAR2(100 BYTE),
  4  CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("DEP#")
  5  );

Table created.

demo@XEPDB1>
demo@XEPDB1> CREATE TABLE "EMPLOYEE"
  2  ( "EMP#" NUMBER(6,0) NOT NULL ENABLE,
  3  "NAME" VARCHAR2(100 BYTE),
  4  "DEP#" VARCHAR2(20 BYTE),
  5  CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMP#"),
  6  CONSTRAINT "EMPLOYEE_FK1" FOREIGN KEY ("DEP#")
  7  REFERENCES "DEPARTMENT" ("DEP#") DEFERRABLE ENABLE
  8  );

Table created.

demo@XEPDB1> create index employee_dept_idx on employee(dep#);

Index created.

demo@XEPDB1> INSERT INTO DEPARTMENT VALUES('ACC','Accounting');

1 row created.

demo@XEPDB1> INSERT INTO DEPARTMENT VALUES('SAL','Sales');

1 row created.

demo@XEPDB1>
demo@XEPDB1> INSERT INTO EMPLOYEE VALUES(1,'John','SAL');

1 row created.

demo@XEPDB1> INSERT INTO EMPLOYEE VALUES(2,'Mary','SAL');

1 row created.

demo@XEPDB1> INSERT INTO EMPLOYEE VALUES(3,'Peter','ACC');

1 row created.

demo@XEPDB1> COMMIT;

Commit complete.

demo@XEPDB1> SET CONSTRAINT EMPLOYEE_FK1 DEFERRED;

Constraint set.

demo@XEPDB1> UPDATE DEPARTMENT SET DEP#='SALES' WHERE DEP#='SAL';

1 row updated.

demo@XEPDB1> UPDATE EMPLOYEE SET dep#='SALES' WHERE emp#=1;

1 row updated.

demo@XEPDB1> COMMIT;

Commit complete.

demo@XEPDB1> select * from department ;

DEP#                 NAME
-------------------- ------------------------------
ACC                  Accounting
SALES                Sales

demo@XEPDB1> select * from employee ;

      EMP# NAME                           DEP#
---------- ------------------------------ --------------------
         1 John                           SALES
         2 Mary                           SAL
         3 Peter                          ACC

demo@XEPDB1>

Just a supposition ...

Iudith Mentzel, June 03, 2022 - 11:33 am UTC

Hello All,
As by the sample, it looks like the problem resides in the order in which Oracle performs its operations.
It looks like, maybe, in the case of a DEFERRED constraint, the index update is postponed to the commit time of the corresponding row(s) changes.
So, at commit time, when the DEPARTMENTS update is checked,
the constraint check mechanism , "knowing" that it can use the FK INDEX for checking the FK constraint and "seeing" that there are no changes done (yet) in the INDEX, it probably bypasses the FK check completely.
This could explain why the problem is NOT encountered without the index or when updating the EMPLOYEES table (and the FK index) before the DEPARTMENTS table.
As I said ... just a supposition ...
But, the situation is a bug anyway.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.