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/