Get all the rows referencing (via foreign keys) a

Question

This seems so simple, but I haven't been able to find an answer to this question.

What do I want? A master table with rows that delete themselves whenever they are not referenced (via foreign keys) anymore. The solution may or may not be specific to PostgreSql.

How? One of my approaches to solving this problem (actually, the only approach so far) involves the following: For every table that references this master table, on UPDATE or DELETE of a row, to check for the referenced row in master, how many other other rows still refer to the referenced row. If it drops down to zero, then I delete that row in master as well.

(If you have a better idea, I'd like to know!)

In detail: I have one master table referenced by many others

CREATE TABLE master (
  id serial primary key,
  name text unique not null
);

All the other tables have the same format generally:

CREATE TABLE other (
  ...
  master_id integer references master (id)
  ...
);

If one of these are not NULL, they refer to a row in master. If I go to this and try to delete it, I will get an error message, because it is already referred to:

ERROR:  update or delete on table "master" violates foreign key constraint "other_master_id_fkey" on table "other"
DETAIL:  Key (id)=(1) is still referenced from table "other".
Time: 42.972 ms

Note that it doesn't take too long to figure this out even if I have many tables referencing master. How do I find this information out without having to raise an error?

Answer 1

You can do one of the following:

1) Add reference_count field to master table. Using triggers on detail tables increase the reference count whenever a row with this master_id is added. Decrease the count, when row gets deleted. When reference_count reaches 0 - delete the record.

2) Use pg_constraint table (details here) to get the list of referencing tables and create a dynamic SQL query.

3) Create triggers on every detail table, that deletes master_id in main table. Silence error messages with BEGIN ... EXCEPTION ... END.

Answer 2

SELECT * 
FROM master ma
WHERE EXISTS (
    SELECT *
    FROM other ot
    WHERE ot.master_id = ma.id
    );

Or, the other way round:

SELECT * 
FROM other ot
WHERE EXISTS (
    SELECT *    
    FROM master ma
    WHERE ot.master_id = ma.id
    );

SO if you want to update (or delete) only the rows in master that are not referenced by other, you could:

UPDATE master ma
SET id = 1000+id
  , name = 'blue'
WHERE name = 'green'
AND NOT EXISTS (
    SELECT *
    FROM other ot
    WHERE ot.master_id = ma.id
    );

Answer 3

In case someone wants a real count of rows in all other tables that reference a given master row, here is some PL/pgSQL. Note that this works in plain case with single column constraints. It gets more involved for multi-column constraints.

CREATE OR REPLACE FUNCTION count_references(master regclass, pkey_value integer,
    OUT "table" regclass, OUT count integer)
    RETURNS SETOF record 
    LANGUAGE 'plpgsql'
    VOLATILE 
AS $BODY$
declare
  x record;           -- constraint info for each table in question that references master
  sql text;           -- temporary buffer
begin
  for x in
    select conrelid, attname
    from pg_constraint
    join pg_attribute on conrelid=attrelid and attnum=conkey[1]
    where contype='f' and confrelid=master
      and confkey=( -- here we assume that FK references master's PK
        select conkey
        from pg_constraint
        where conrelid=master and contype='p'
      )
  loop
    "table" = x.conrelid;
    sql = format('select count(*) from only %s where %I=$1', "table", x.attname);
    execute sql into "count" using pkey_value;
    return next;
  end loop;
end
$BODY$;

Then use it like

select * from count_references('master', 1) where count>0

This will return a list of tables that have references to master table with id=1.