Subject Is this a bug or intended behaviour?
Author Thomas

consider the following sample data:

create table fktest (
id integer primary key not null,
name varchar(20),
parent_id integer
alter table fktest add constraint fktest_parent foreign key (parent_id) references fktest(id);

insert into fktest (id,name,parent_id) values (1,'Root', null);
insert into fktest (id,name,parent_id) values (2,'Sub1', 1);
insert into fktest (id,name,parent_id) values (3,'Subsub', 2);
insert into fktest (id,name,parent_id) values (4,'Sub2', 1);

Now when I run the following statement:

delete from fktest where id in (1,2,3,4);

I get the following error

GDS Exception. 335544466. violation of FOREIGN KEY constraint "FKTEST_PARENT" on table "FKTEST"
Foreign key references are present for the record [SQL State=HY000, DB Errorcode=335544466]

which confuses me a bit. As the DELETE is a single transaction, shouldn't this be possible?

Is my assumption (that this should be working) wrong?