pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Murray Hobbs <murray@efone.com>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: protected ON DELETE CASCADE
Date: Fri, 24 Aug 2001 08:46:16 +1200
Message-ID: <3B856B98.86CA06CA@efone.com> (raw)
References: <200108232021.f7NKL7b02620@jupiter.us.greatbridge.com>


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



view thread (7+ messages)

Message-ID: <3B856B98.86CA06CA@efone.com>
Permalink:  ../3B856B98.86CA06CA@efone.com/
Also on:    postgresql.org/message-id/3B856B98.86CA06CA@efone.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: murray@efone.com, JanWieck@Yahoo.com, pgsql-general@postgresql.org
  Subject: Re: protected ON DELETE CASCADE
  In-Reply-To: <3B856B98.86CA06CA@efone.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox