Re: deleting referenced data

On Wed, Aug 27, 2003 at 00:09:34 +0200,
Andreas Fromm <Andreas(dot)Fromm(at)physik(dot)uni-erlangen(dot)de> wrote:
> Bruno Wolff III wrote:
>
> >On Tue, Aug 26, 2003 at 18:34:04 +0100,
> > Richard Huxton <dev(at)archonet(dot)com> wrote:
> >
> >
> >I don't think that is what he wants. He seems to want to go in the other
> >direction. That is going to require custom triggers and probably adding a
> >reference count column to data-tables.
> >
> >
> This is what I want to do, but my question is how can I easely now if
> there is someone referencing a certain record. Is it just possible by
> searching every table that could reference it or is there an other way
> of doing it without adding an reference count column?

You can use a reference count in the row you are interested in. You
would need to create triggers on all referncing tables to update the
reference count as necessary. Also as pointed out previously, you
want to use serialized isolation (and handle retries) or exclusively lock
the table (and worry about deadlocks and a concurrency bottleneck)
if you use this approach.

You could do a scan of all possible referencing tables whenever you update
any of the referencing tables. However, this approach has pretty much the
same problems as using a refernce count and will probably be slower.