Thread: BUG #3627: Triple FK with ON DELETE SET NULL makes DELETE fail

BUG #3627: Triple FK with ON DELETE SET NULL makes DELETE fail

From
"Pedro Gimeno"
Date:
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

Re: BUG #3627: Triple FK with ON DELETE SET NULL makes DELETE fail

From
"Heikki Linnakangas"
Date:
Pedro Gimeno wrote:
> 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

This was fixed in 8.2.5, see this bug report:

http://archives.postgresql.org/pgsql-bugs/2007-08/msg00086.php

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com