ON DELETE CASCADE with multiple paths

From: Max Khon <mkhon(at)swsoft(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: ON DELETE CASCADE with multiple paths
Date: 2007-05-17 12:55:44
Message-ID: 464C50D0.2010702@swsoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)swsoft(dot)com
Web Site: http://swsoft.com/

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2007-05-17 14:50:15 Re: ON DELETE CASCADE with multiple paths
Previous Message Heikki Linnakangas 2007-05-17 10:38:02 Re: BUG #3289: SIN(PI()) expected to return 0, but returns garbage