lost on referentail integrity

Started by Patrice Beliveauover 19 years ago2 messagesbugs
Jump to latest
#1Patrice Beliveau
pbeliveau@avior.ca

Hi,

I'm using PostgreSQL 8.1.4

The problem is very simple to reproduce:

create table test1 (
pk1 text not null,
df1 text,
primary key(pk1)
);

create table test2 (
pk1 text not null,
pk2 text not null,
df2 text,
primary key(pk1,pk2)
);

alter table test2 add constraint c1 foreign key (pk1) references test1(pk1)
on update cascade
on delete cascade;

create or replace function f1() returns "trigger" as '
begin
return null;
end;
' language "plpgsql";

create trigger trig1 before delete on test2 for each row execute
procedure f1();

insert into test1 values('foo','foo');

insert into test2 values('foo','oof','foooof');

delete from test1;

select * from test2;
pk1 | pk2 | df2
-----+-----+--------
foo | oof | foooof
(1 row)

select * from test1;
pk1 | df1
-----+-----
(0 rows)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Patrice Beliveau (#1)
Re: lost on referentail integrity

Patrice Beliveau <pbeliveau@avior.ca> writes:

The problem is very simple to reproduce:

[ shrug... ] Your trigger suppressed the cascaded deletes. What were
you expecting to happen?

regards, tom lane