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