Thread: protected ON DELETE CASCADE
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 can someone who knows the pgsql system well tell me how this is best done I've looked at RULE - but how do i delete the old. record (ie confirm the delete) or conversely prevent the delete? I've looked at TRIGGER but then i'm writing a function (in SQL?) which is called by a trigger - again - how do i confirm or reject a delete? or is it really the case i have to maintain the integrity externally and write functions that deal with the children and then delete parents - all in a single transaction - ie not use ON DELETE CASCADE, or rules, or triggers? and sorry for cross post but was not sure which was the best list thanks murray hobbs efone.com
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
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
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
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 > >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 B REFERENCES A ON DELETE CASCADE C REFERENCES A ON DELETE CASCADE D REFERENCES C ON DELETE NO ACTION E REFERENCES C ON DELETE CASCADE F REFERENCES C ON DELETE CASCADE So if there is a deletion in A it cascades to B and C The deletion in C cascades to E and F, but fails if there is a reference in D. The failure causes a rollback of the transaction and thus the deletions in A, B, C, E and F do not happen after all. -- 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
ok *click* i get it i get it m Oliver Elphick 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 > > > >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 > > B REFERENCES A ON DELETE CASCADE > C REFERENCES A ON DELETE CASCADE > D REFERENCES C ON DELETE NO ACTION > E REFERENCES C ON DELETE CASCADE > F REFERENCES C ON DELETE CASCADE > > So if there is a deletion in A it cascades to B and C > > The deletion in C cascades to E and F, but fails if there is a > reference in D. The failure causes a rollback of the transaction and > thus the deletions in A, B, C, E and F do not happen after all. > > -- > 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
On Thu, 23 Aug 2001, Murray Hobbs wrote: > 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 what about ON DELETE RESTRICT there? > I've looked at TRIGGER but then i'm writing a function (in SQL?) which > is called by a trigger - again - how do i confirm or reject a delete? Look at the docs: there is a difference between DO and DO INSTEAD (I think, I am no expert on Pg, and I do not have access to a Pg system right now). Albert.