Received: from mta2-rme.xtra.co.nz (mta2-rme.xtra.co.nz [203.96.92.3]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7NKbVP05853; Thu, 23 Aug 2001 16:37:31 -0400 (EDT) (envelope-from murray@efone.com) Received: from efone.com ([210.55.87.180]) by mta2-rme.xtra.co.nz with ESMTP id <20010823203720.HKCF703.mta2-rme.xtra.co.nz@efone.com>; Fri, 24 Aug 2001 08:37:20 +1200 Message-ID: <3B856B98.86CA06CA@efone.com> Date: Fri, 24 Aug 2001 08:46:16 +1200 From: Murray Hobbs X-Mailer: Mozilla 4.7 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: Jan Wieck CC: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: protected ON DELETE CASCADE References: <200108232021.f7NKL7b02620@jupiter.us.greatbridge.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/744 X-Sequence-Number: 14148 cool, thanks yes, i was slack reading th docs m Jan Wieck wrote: > > Murray Hobbs wrote: > > > > i neglected to show it properly > > > > have tables A, B, C, D PLUS a few others > > > > A <- B > > > > F > > | > > v > > A <- C <- D > > ^ > > | > > E > > > > i want to delete from C and cascade any delete to E or F but not if > > there are records in D > > > > what i have done is to have ON DELETE CASCADE on C's primary > > How? You cannot specify the ON DELETE behaviour on the > primary key. You specify it on the foreign key definition, > and there's no reason why these definitions may not be > different between D, E and F. > > > > > but force deletes to C through a function that will delete from C only > > if there is no records in D > > Exactly that is the JOB of a foreign key constraint, or do > you want to silently suppress the delete from C instead of > bailing out with a transaction abort? > > > > > but i would like to believe there is a better way - a way that does not > > require that i do all my deletes through a function > > Why doesn't this work for you? > > CREATE TABLE A ( > aa integer, > > PRIMARY KEY (aa) > ); > > CREATE TABLE C ( > ca integer, > cc integer, > > PRIMARY KEY (ca, cc), > FOREIGN KEY (ca) REFERENCES A (aa) ON DELETE CASCADE > ); > > CREATE TABLE D ( > da integer, > dc integer, > > FOREIGN KEY (da, dc) REFERENCES C (ca, cc) > ); > > CREATE TABLE E ( > ea integer, > ec integer, > > FOREIGN KEY (ea, ec) REFERENCES C (ca, cc) ON DELETE CASCADE > ); > > CREATE TABLE F ( > fa integer, > fc integer, > > FOREIGN KEY (fa, fc) REFERENCES C (ca, cc) ON DELETE CASCADE > ); > > With this setup, you will not be able to delete any data from > A or C that is referenced from D. Anything else is deletable > and will cause referencing rows from C, E and F to go away as > well. > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster