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
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
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
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
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
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.