Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group