[SQL] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: [GENERAL] protected ON DELETE CASCADE
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[EMAIL PROTECTED] > 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. # > #== [EMAIL PROTECTED] # > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [GENERAL] protected ON DELETE CASCADE
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. # > #== [EMAIL PROTECTED] # > > _ > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
