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
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