Re: deleting a foreign key that has no references

Glen W. Mabey wrote:
> On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote:
>
>> On 3/19/07, Glen W. Mabey <Glen.Mabey@swri.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.
>
This query will return the list of foreign keys which refer to primary keys:

SELECT
   g as "DB",n.nspname as "PK_schema",pc.relname as
"PK_table",pa.attname as "PK_column",
   n.nspname as "FK_schema",c.relname as "FK_table",a.attname as
"FK_column",b.n as "FK_column_number", f.conname as "FK_name",
   pr.conname as "PK_name"
FROM
  current_database()g,pg_catalog.pg_attribute a,pg_catalog.pg_attribute
pa,pg_catalog.pg_class c,pg_catalog.pg_class pc,pg_catalog.pg_namespace n,
  pg_catalog.pg_namespace pn,pg_catalog.pg_constraint f left join
pg_catalog.pg_constraint pr on(f.conrelid=pr.conrelid and pr.contype='p'),
  (SELECT * FROM
generate_series(1,current_setting('max_index_keys')::int,1))b(n)
WHERE
  n.oid=c.relnamespace AND pn.oid=pc.relnamespace AND pc.oid=f.confrelid
AND c.oid=f.conrelid AND pa.attrelid=f.confrelid AND a.attrelid=f.conrelid
  AND pa.attnum=f.confkey[b.n]AND a.attnum=f.conkey[b.n]AND
f.contype='f'AND f.conkey[b.n]<>0 AND has_schema_privilege(n.oid,
'USAGE'::text);

Add conditions to the pr.conname and you will get what you need