Re: protected ON DELETE CASCADE

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com