Following script was tested during re-implementing test for CORE-4212.
Please note on lines marked as [1] and [2]
set list on;
shell del g:\temp\tmp4test.fdb 2>nul;
create database 'localhost:g:\temp\tmp4test.fdb';
create table persistent_main (
id int not null,
primary key (id)
);
create table persistent_detl (id int);
alter table persistent_detl add constraint tdetl_fk foreign key (id) references persistent_main (id);
commit;
insert into persistent_detl(id) values(1);
commit;
connect 'localhost:g:\temp\tmp4test.fdb'; -------------------------------------------------------- [ !!! 1 !!! ]
set autoddl off;
commit;
alter table persistent_detl drop constraint tdetl_fk;
rollback; -------------------------------------------------------- [ !!! 2 !!! ]
set echo on;
show table persistent_detl;
commit;
insert into persistent_detl(id) values(2);
select d.id as orphan_child_id
from persistent_detl d
where not exists(select * from persistent_main m where m.id = d.id);
Of course, first attempt to insert row in detail table (while master is empty) will fail:
insert into persistent_detl(id) values(1);
-- produces: SQLSTATE = 23000 / -Foreign key reference target does not exist / -Problematic key value is ("ID" = 1))
But let this script go further.
You can see that there was statement to DROP FK but it was rolled back (see line marked as [ !!! 2 !!! ]).
Final output of script will end with:
show table persistent_detl; -- Still exists: "CONSTRAINT TDETL_FK: Foreign key (ID) References
ID INTEGER Nullable
CONSTRAINT TDETL_FK:
Foreign key (ID) References PERSISTENT_MAIN (ID)
commit;
insert into persistent_detl(id) values(2); -- NO error here! Why ?
select d.id as orphan_child_id
from persistent_detl d
where not exists(select * from persistent_main m where m.id = d.id);
ORPHAN_CHILD_ID 2
What is most interesting: such outcome (presence of 'orphan' record in the detailed table) will NOT occur if we comment out line marked as [ !!! 1 !!! ]
Following script was tested during re-implementing test for CORE-4212.
Please note on lines marked as [1] and [2]
Of course, first attempt to insert row in detail table (while master is empty) will fail:
insert into persistent_detl(id) values(1);-- produces:
SQLSTATE = 23000 / -Foreign key reference target does not exist / -Problematic key value is ("ID" = 1))But let this script go further.
You can see that there was statement to DROP FK but it was rolled back (see line marked as
[ !!! 2 !!! ]).Final output of script will end with:
What is most interesting: such outcome (presence of 'orphan' record in the detailed table) will NOT occur if we comment out line marked as
[ !!! 1 !!! ]