Description:
I have three tables A, B and C, whereas the latter two reference the first (DELETE RULE RESTRICT). Table A contains data and table C also contains data, therefore
deleting referenced rows in A should lead to a foreign key violation, but does not, if the foreign keys of B,C are creating in the "wrong" order.
How to repeat:
Minimal Test :
-> The last delete should fail, but does not.
But if you reorder of foreign key creation - C before B, then
the last statement will fail.
-> This test describes a minimal environment. It needs to have a primary
key and a unique key on A, whereas the unique key contains a nullable column.
And B references the unique key of A, and C references the primary key
of A twice.
SQL - Script :
CREATE TABLE "TEST_A" ( "INST_ID" FIXED(18,0) NOT NULL , "APP_ID" FIXED(18,0) , "ID" FIXED(18,0) NOT NULL )
CREATE TABLE "TEST_B" ( "INST_ID" FIXED(18,0) NOT NULL , "APP_ID" FIXED(18,0) NOT NULL , "A_ID" FIXED(18,0) NOT NULL , "ID" FIXED(18,0) NOT NULL )
CREATE TABLE "TEST_C" ( "INST_ID" FIXED(18,0) NOT NULL , "PARENT_A_ID" FIXED(18,0) NOT NULL , "CHILD_A_ID" FIXED(18,0) NOT NULL )
COMMIT
ALTER TABLE "TEST_A" ADD CONSTRAINT "PK_TEST_A" PRIMARY KEY ( "INST_ID" , "ID" )
ALTER TABLE "TEST_A" ADD CONSTRAINT "UK_TEST_A" UNIQUE ( "INST_ID" , "APP_ID" , "ID" )
ALTER TABLE "TEST_B" ADD CONSTRAINT "PK_TEST_B" PRIMARY KEY ( "INST_ID" , "APP_ID" , "ID" )
ALTER TABLE "TEST_C" ADD CONSTRAINT "PK_TEST_C" PRIMARY KEY ( "INST_ID" , "PARENT_A_ID" , "CHILD_A_ID" )
COMMIT
INSERT INTO "TEST_A" ( "INST_ID" , "APP_ID" , "ID" ) VALUES ( 0 , 0 , 0 )
INSERT INTO "TEST_A" ( "INST_ID" , "APP_ID" , "ID" ) VALUES ( 0 , NULL , 1 )
COMMIT
INSERT INTO "TEST_C" ( "INST_ID" , "PARENT_A_ID" , "CHILD_A_ID" ) VALUES ( 0 , 0 , 1 )
COMMIT
ALTER TABLE "TEST_B" ADD CONSTRAINT "FK_TEST_B_A" FOREIGN KEY ( "INST_ID" , "APP_ID" , "A_ID" ) REFERENCES "TEST_A" ( "INST_ID" , "APP_ID" , "ID" )
ALTER TABLE "TEST_C" ADD CONSTRAINT "FK_TEST_C_A_CHLD" FOREIGN KEY ( "INST_ID" , "CHILD_A_ID" ) REFERENCES "TEST_A" ( "INST_ID" , "ID" )
ALTER TABLE "TEST_C" ADD CONSTRAINT "FK_TEST_C_A_PRNT" FOREIGN KEY ( "INST_ID" , "PARENT_A_ID" ) REFERENCES "TEST_A" ( "INST_ID" , "ID" )
COMMIT
DELETE FROM "TEST_A" WHERE "INST_ID" = 0 AND "ID" = 0
