Received: from danbo.digsys.bg (root@danbo.digsys.bg [192.92.129.10]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA30614 for ; Wed, 17 Nov 1999 08:43:02 -0500 (EST) (envelope-from genov@lorna.digsys.bg) Received: from lorna.digsys.bg (root@lorna.digsys.bg [193.68.6.15]) by danbo.digsys.bg (8.9.0/8.9.0) with ESMTP id PAA10384 for ; Wed, 17 Nov 1999 15:42:49 +0200 (EET) Received: from lorna.digsys.bg (genov@localhost.digsys.bg [127.0.0.1]) by lorna.digsys.bg (8.9.0/8.9.0) with ESMTP id PAA19426 for ; Wed, 17 Nov 1999 15:42:47 +0200 (EET) Message-Id: <199911171342.PAA19426@lorna.digsys.bg> X-Mailer: exmh version 2.0.2 2/24/98 To: pgsql-sql@postgresql.org Subject: Building "ON DELETE" rules to keep the referential integrity of a database From: "Stoyan Genov" Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Wed, 17 Nov 1999 15:42:46 +0200 Sender: genov@digsys.bg Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id IAA30643 Hi, I cannot figure out how to construct an "ON DELETE" rule to keep the referential integrity. Having tables t1 (id, ... ), t2 (t1_id, ...), the best I got to was: CREATE RULE t1_t2_del AS ON DELETE TO t1 DO DELETE FROM t2 WHERE t2.t1_id NOT IN (SELECT id FROM t1); which deletes records from t2 "a step back" - it deletes already "orphaned" records from t2, and not those being orphaned by the current DELETE. I would like also to build a rule which works on the "reverse DELETE" condition, and disallows deleting t1 records if there is a "child" in t2 present. Does somebody have any ideas? Thanks in advance. Stoyan Genov