ON DELETE CASCADE with multiple paths - Mailing list pgsql-bugs

From Max Khon
Subject ON DELETE CASCADE with multiple paths
Date
Msg-id 464C50D0.2010702@swsoft.com
Whole thread Raw
Responses Re: ON DELETE CASCADE with multiple paths  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Hi!

Suppose the following schema:

create table foo(foo_id integer primary key);
create table bar(bar_id integer primary key, foo_id integer,
    constraint bar_fk0 foreign key (foo_id) references foo(foo_id)
    on delete cascade);
create table foobar(foobar_id serial primary key, foo_id integer, bar_id
integer);

alter table foobar add constraint foobar_fk1 foreign key (bar_id)
references bar(bar_id) on delete cascade;
alter table foobar add constraint foobar_fk0 foreign key (foo_id)
references bar(bar_id);

And data:

insert into foo(foo_id) values(1);
insert into bar(bar_id, foo_id) values(1, 1);
insert into foobar(foo_id, bar_id) values(1, 1);

The following statement does work:

delete from foo;

All is ok. The row in foobar is deleted because of ON DELETE CASCADE fk
constraints bar_fk0 and foobar_fk1.

After altering the schema:

alter table foobar drop constraint foobar_fk0;
alter table foobar drop constraint foobar_fk1;

alter table foobar add constraint foobar_fk0 foreign key (foo_id)
references bar(bar_id);
alter table foobar add constraint foobar_fk1 foreign key (bar_id)
references bar(bar_id) on delete cascade;

(note that constraints are now added in different order)

and the same data:

insert into foo(foo_id) values(1);
insert into bar(bar_id, foo_id) values(1, 1);
insert into foobar(foo_id, bar_id) values(1, 1);

"delete from foo" fails:

ERROR: update or delete on table "bar" violates foreign key constraint
"foobar_fk0" on table "foobar"
SQL state: 23503
Detail: Key (bar_id)=(1) is still referenced from table "foobar".
Context: SQL statement "DELETE FROM ONLY "public"."bar" WHERE "foo_id" = $1"

PostgreSQL version: any (I tested on 8.2.4 for Win32 and 8.1.3 for Linux)

--
Max Khon
PEM Platform Team Leader
SWsoft, Inc.
E-mail: mkhon@swsoft.com
Web Site: http://swsoft.com/

pgsql-bugs by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: BUG #3289: SIN(PI()) expected to return 0, but returns garbage
Next
From: Tom Lane
Date:
Subject: Re: ON DELETE CASCADE with multiple paths