Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com [216.136.173.57]) by postgresql.org (8.11.3/8.11.4) with SMTP id f7NKB1P96446 for ; Thu, 23 Aug 2001 16:11:01 -0400 (EDT) (envelope-from janwieck@yahoo.com) Received: from jupiter.us.greatbridge.com (65.196.69.55) by smtp.mail.vip.sc5.yahoo.com with SMTP; 23 Aug 2001 20:10:59 -0000 X-Apparently-From: Received: (from wieck@localhost) by jupiter.us.greatbridge.com (8.11.2/8.11.2) id f7NKL7b02620; Thu, 23 Aug 2001 16:21:07 -0400 From: Jan Wieck Message-Id: <200108232021.f7NKL7b02620@jupiter.us.greatbridge.com> Subject: Re: protected ON DELETE CASCADE In-Reply-To: <3B855488.43799073@efone.com> from Murray Hobbs at "Aug 24, 2001 07:07:52 am" To: Murray Hobbs Date: Thu, 23 Aug 2001 16:21:07 -0400 (EDT) CC: Jan Wieck , pgsql-sql@postgresql.org, pgsql-general@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/738 X-Sequence-Number: 14142 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