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@Yahoo.com #



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