Re: deleting a foreign key that has no references

On Mon, 2007-03-19 at 11:12 -0500, Glen W. Mabey wrote:
> 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.

In the easy case when your schema doesn't change often, you can just
hard code a query of the FK tables and add up the row counts. I bet
something like 'select count(*) from (select * from FKtable1 UNION ALL
select * from FKtable2 ... )' will work (and I'm guessing that the UNION
ALL will optimize well). Obviously, you want indexes on the FKs.

The harder and more general case is to build such a query dynamically
from pg_depends. A good start would be to write a function that returns
an sql query like the above to count the referents of PKtable(PKcolumn).
If you can declare this function stable or immutable (I'm not sure of
this), then it might not be too painful to generate the query within the
trigger itself. Otherwise, you might have to store/update these queries
in a separate table after every DDL change.

See the pg_depends documentation at
http://www.postgresql.org/docs/8.2/interactive/catalog-pg-depend.html

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.