Thomas wrote:

> Hi,
>
> 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);
> commit;
>
> 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);
> commit;
>
> 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?

Err, well the assumption that it should work because the deletes are
all in the same transaction is wrong. Firebird doesn't do deferred
constraints (yet). I think it should work because all the deletes are
in the same statement and (in theory) the constraint should be checked
at the end of the statement, not row-by-row. However, most everything
in Firebird is row-by-row, so this is consistent, if not correct. Did
you try

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

?

Cheers,


Ann