Received: from mta6-rme.xtra.co.nz (mta6-rme.xtra.co.nz [203.96.92.19]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7MLqTP43041; Wed, 22 Aug 2001 17:52:29 -0400 (EDT) (envelope-from murray@efone.com) Received: from efone.com ([210.55.85.227]) by mta6-rme.xtra.co.nz with ESMTP id <20010822215212.SXBD1983894.mta6-rme.xtra.co.nz@efone.com>; Thu, 23 Aug 2001 09:52:12 +1200 Message-ID: <3B842BA3.D7E3930D@efone.com> Date: Thu, 23 Aug 2001 10:01:07 +1200 From: Murray Hobbs X-Mailer: Mozilla 4.7 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org, pgsql-novice@postgresql.org, pgsql-general@postgresql.org Subject: protected ON DELETE CASCADE Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/691 X-Sequence-Number: 14081 here's my problem i have tables A, B, C, D A <- B A <- C <- D i want to maintain integrity so that if A is deleted from then so is anything referencing from B and C - no problem ON DELETE CASCADE but if there are any D's that point back to A (through composite key in C) i don't want the delete to go ahead - at all - i want an error message and condition can someone who knows the pgsql system well tell me how this is best done I've looked at RULE - but how do i delete the old. record (ie confirm the delete) or conversely prevent the delete? I've looked at TRIGGER but then i'm writing a function (in SQL?) which is called by a trigger - again - how do i confirm or reject a delete? or is it really the case i have to maintain the integrity externally and write functions that deal with the children and then delete parents - all in a single transaction - ie not use ON DELETE CASCADE, or rules, or triggers? and sorry for cross post but was not sure which was the best list thanks murray hobbs efone.com