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
