The following bug has been logged online:
Bug reference: 3627
Logged by: Pedro Gimeno
Email address: pgsql-001@personal.formauri.es
PostgreSQL version: 8.2.4
Operating system: Linux (Debian stable + backports)
Description: Triple FK with ON DELETE SET NULL makes DELETE fail
Details:
In the following script:
-- begin script
create database test1;
\c test1
create table t1 (id integer not null primary key);
create table t2 (id integer not null primary key,
f1 integer references t1(id) on delete set null,
f2 integer references t1(id) on delete set null,
f3 integer references t1(id) on delete set null);
insert into t1 values (1);
insert into t1 values (2);
insert into t2 values (3,1,1,1);
insert into t2 values (4,2,2,2);
delete from t1 where id=1;
-- end script
the last delete results in the following error:
ERROR: insert or update on table "t2" violates foreign key constraint
"t2_f3_fkey"
DETAIL: Key (f3)=(1) is not present in table "t1".
CONTEXT: SQL statement "UPDATE ONLY "public"."t2" SET "f2" = NULL WHERE "f2"
= $1"
-- Pedro Gimeno