Search Postgresql Archives

Re: deleting a foreign key that has no references

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux