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