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
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.