Re: Determine if any foreign keys exist?
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/29/04
- Next message: John Bell: "Re: Sending FAX from SQL data"
- Previous message: Bernie Yaeger: "xp_cmdshell syntax problem"
- In reply to: Brian Vallelunga: "Re: Determine if any foreign keys exist?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 30 Jun 2004 01:07:52 +0200
On Tue, 29 Jun 2004 18:47:46 -0400, Brian Vallelunga wrote:
>
>Hugo Kornelis wrote:
>
>> By the way, may I ask why you need this? What is the actual business
>> problem you are trying to solve? Maybe there are other options that
>> achieve the same with much less hassle!
>>
>> Best, Hugo
>
>
>Basically what I am trying to do is to avoid having orphaned records in
>a parent table. If I have one parent table and ten child tables, I want
>the parent record deleted when no child table references it anymore.
>Cascading delete won't work (I don't think) because it doesn't check if
>the parent record is still being referenced by other child tables. One
>option would be to use a trigger on the child tables, which I considered
>the tedius route as each trigger for a table would have to check each
>other child table, and thus the maintence hassle when adding a new child
>table to the mix (no so much a reshuffle as an addition of a feature
>usually). This just seems like it would be a very common problem that
>I'm surprised I haven't seen an obvious solution for it yet. I might
>just go with the trigger route to create what is essentially a
>"conditional cascading delete".
>
>Thanks for the help.
>
>Brian
Hi Brian,
You're right, cascading delete won't do this. That is only good for
deleting childs when the parent gets deleted, not the other way around.
I think that there are three options to consider:
1. Do it as you suggested, putting a trigger on each child table that will
check all other child tables. Copy and paste is your friend if you make
this, and it still is if you add more child tables later. Make sure that
proper indexes are in place to speed up the checking of existance in the
child tables.
2. As an alternative, have an extra column in the parent table indicating
the number of referendces to the parent row. Increase it in an insert
trigger, decrease it in a delete trigger, increase one and decrease
another in an update trigger. Delete parent row when counter hits 0. This
option might yield better performance than the first option, but it might
just as well hamper performance - test it against your typical daily load
of changes.
3. If the trigger overhead slows things down to much and a short lag is
not a problem, set up a process that runs at specified intervals, checking
ALL rows in the parent table against all child tables and deleting all
orphaned parents (an oxymoron if there ever was one <g>). If the number of
rows in the parent table is large and the update frequency of the children
is low, you might also have triggers on the children that just write the
primary key to an intermediate table; the cleanup process will than check
only the parents whose PK is in that table (and emtpy that table, of
course).
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: John Bell: "Re: Sending FAX from SQL data"
- Previous message: Bernie Yaeger: "xp_cmdshell syntax problem"
- In reply to: Brian Vallelunga: "Re: Determine if any foreign keys exist?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
