Re: protected ON DELETE CASCADE - Mailing list pgsql-general

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