Re: deleting a foreign key that has no references

On Mon, Mar 19, 2007 at 09:46:22AM -0700, Stephan Szabo wrote:
> On Mon, 19 Mar 2007, Glen W. Mabey wrote:
>
> > Hello,
> >
> > 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;
>
> Was that the actual function you used or just a shortened version? A
> function like that with a delete of the referenced table in the body for
> the appropriate key appeared to have reasonable behavior on my 8.2 system
> with an immediate constraint, but I didn't do very much testing. One issue
> is that to test the insert of a row into the referenced table you'd
> probably need to defer a check that the row is referenced in order to have
> time to insert referencing rows.

Okay, it turns out that I only had not implemented the exception catch
appropriately. Here's what worked:

BEGIN
DELETE FROM "Cuts" WHERE "Cuts".id = OLD.cut_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
RETURN NULL;

andyk: Thank you for the SELECT string you contributed. Unfortunately,
I could not understand what it was doing -- it was way over my head WRT
psql proficiency. So, I don't know whether it would have worked.

At any rate, thank you all for your suggestions. Testing for an error
seems to be the simplest and easiest way to accomplish what I need to
do, and it seems to be fairly fast, too.

Best Regards,
Glen Mabey