Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 66D064759A7 for ; Sun, 10 Mar 2002 17:35:26 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g2AMJSV35592 for pgsql-sql@postgresql.org; Sun, 10 Mar 2002 16:19:28 -0600 (CST) (envelope-from news) From: "msn" Subject: Referential integrity implementation - bug or user error? Date: Sun, 10 Mar 2002 23:19:34 +0100 X-Newsgroups: comp.databases.postgresql.sql Message-ID: <20020310.231934.654887343.1425@post.tele.dk> User-Agent: Pan/0.9.7 (Unix) Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Lines: 53 Organization: TDC Internet X-Complaints-To: abuse@post.tele.dk To: pgsql-sql@postgresql.org X-Archive-Number: 200203/155 X-Sequence-Number: 7035 Hi, Postgres fails to set null values when referenced key is deleted. I think I will try to explain with this simple example which I have tested on my linux box (rhat7.2) running postgres-v7.2. I have two tables as follows create table t1 ( id integer not null primary key ); create table t2 ( id integer not null primary key, t1_aid integer, t1_bid integer, foreign key (t1_aid) references t1 (id) on delete set null on update cascade, foreign key (t1_bid) references t1 (id) on delete set null on update cascade ); ... and then I add some values as in msn=# insert into t1 values (1); INSERT 16904 1 msn=# insert into t1 values (2); INSERT 16905 1 msn=# insert into t2 values (1, 1, 1); INSERT 16906 1 msn=# insert into t2 values (2, 2, 2); INSERT 16907 1 msn=# insert into t2 values (3, 1, 2); INSERT 16908 1 But then when I try to delete one id from t1 I get this error message. msn=# delete from t1 where id=1; ERROR: referential integrity violation - key referenced from t2 not found in t1 Why this fails to set columns t1_aid and t1_bid in table t2 to null? Any help is greatly appreciated. Thanks. vujadin