pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <JanWieck@Yahoo.com>
To: Murray Hobbs <murray@efone.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: protected ON DELETE CASCADE
Date: Thu, 23 Aug 2001 16:21:07 -0400 (EDT)
Message-ID: <200108232021.f7NKL7b02620@jupiter.us.greatbridge.com> (raw)
In-Reply-To: <3B855488.43799073@efone.com>

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




view thread (7+ messages)  latest in thread

Message-ID: <200108232021.f7NKL7b02620@jupiter.us.greatbridge.com>
Permalink:  ../200108232021.f7NKL7b02620@jupiter.us.greatbridge.com/
Also on:    postgresql.org/message-id/200108232021.f7NKL7b02620@jupiter.us.greatbridge.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: JanWieck@Yahoo.com, murray@efone.com, pgsql-general@postgresql.org
  Subject: Re: protected ON DELETE CASCADE
  In-Reply-To: <200108232021.f7NKL7b02620@jupiter.us.greatbridge.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