Re: [GENERAL] protected ON DELETE CASCADE - Mailing list pgsql-sql

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

but force deletes to C through a function that will delete from C only
if there is no records in D

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

cheers

murray



Oliver Elphick wrote:
>
> Murray Hobbs wrote:
>   >
>   >here's my problem
>   >
>   >i have tables A, B, C, D
>   >
>   >A <- B
>   >A <- C <- D
>   >
>   >i want to maintain integrity so that if A is deleted from then so is
>   >anything referencing from B and C - no problem ON DELETE CASCADE
>   >
>   >but if there are any D's that point back to A (through composite key in
>   >C) i don't want the delete to go ahead - at all - i want an error
>   >message and condition
>
> If the reference from D to C uses ON DELETE RESTRICT (or NO ACTION), that
> should fail and thus cause the original DELETE to fail.
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "For God hath not appointed us to wrath, but to obtain
>       salvation by our Lord Jesus Christ, Who died for us,
>       that, whether we wake or sleep, we should live
>       together with him."
>                          I Thessalonians 5:9,10



Jan Wieck wrote:
>
> Murray Hobbs wrote:
> >
> > here's my problem
> >
> > i have tables A, B, C, D
> >
> > A <- B
> > A <- C <- D
> >
> > i want to maintain integrity so that if A is deleted from then so is
> > anything referencing from B and C - no problem ON DELETE CASCADE
> >
> > but if there are any D's that point back to A (through composite key in
> > C) i don't want the delete to go ahead - at all - i want an error
> > message and condition
>
>     So  B  and  C  reference  A  with  ON DELETE CASCADE, while D
>     references C without it. The default behaviour of  a  foreign
>     key  constraint  is  ON  DELETE  NO ACTION, which confusingly
>     enough aborts the transaction (it's defined that way  in  the
>     SQL  standard,  don't  ask  me why they called it NO ACTION).
>     Thus a deletion from A will cascaded delete from C, but  then
>     the  constraint  on  D  will  abort  the  transaction if this
>     automatic delete from C would orphan a reference from D.
>
> 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