Re: deleting a foreign key that has no references

On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote:
> On 3/19/07, Glen W. Mabey <Glen(dot)Mabey(at)swri(dot)org> wrote:
> >I'm using 8.1.8, and I have a situation where a record in one table
> >is
> >only meaningful when it is referenced via foreign key by one or more
> >records in any one of several tables.
> >
> >So, really what I want is when one of the referring records is
> >deleted,
> >to have a trigger check to see if it was the last one to use that
> >foreign key, and if so, to delete that other record, too.
> >
> >My first implementation of this functionality was to write a trigger
> >function that executed a COUNT(*) on all of the tables that could
> >have a
> >reference in them. That became way too slow for the number of
> >records
> >in these tables.
> >
> >Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the
> >foreign
> >key constraint, and then trying to catch the exception thrown when a
> >deletion attempt is made on the record. However, it seems that this
> >PL/pgsql snippet fails to catch such an error:
> >
> > BEGIN EXCEPTION
> > WHEN RAISE_EXCEPTION THEN
> > RETURN NULL;
> > WHEN OTHERS THEN
> > RETURN NULL;
> > END;
> >
> > But, really, I just want to be able to test to see how many
> > references there are to a key. Is there
> > some way to do that?
> >
>
> write a triggers which do that.

I understand that a trigger should be written, and I have already
implemented two such triggers, as described above.

What I'm hoping to find out is whether there is some way to directly
find out how many (using a SELECT query) references there are to a key.

Glen