Re: deleting a foreign key that has no references

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.

> 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?

Currently, not apart from selecting on the referencing table.