Get all the rows referencing (via foreign keys) a particular row in a table

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?

Ihor Romanchenko · Accepted Answer · 2013-01-16T12:06:56.173

5

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.

edited Jan 16 '13 at 12:06

answered Jan 16 '13 at 11:35

Ihor Romanchenko

26,995
8
48
44

The reference count is indeed the only sane way. (after rereading the question) Personally I would never use it though (since it will complicate and slow down every operation), instead I would periodically clean up the unreferenced rows from the master table. – wildplasser Jan 16 '13 at 11:58
Suggestion 3. seems the least clean, but also the easiest. Is there a performance (or any other) penalty for using this kind of behavior. Is it frowned upon for being *hackish* or anything? – cassava Jan 16 '13 at 12:13
PostgreSql has to get the information I am looking for somewhere (otherwise it couldn't enforce the foreign key constraints). How does PostgreSql do it then? With internal reference counting? Considering the short runtime, I can hardly see that it would scan all the tables. – cassava Jan 16 '13 at 12:15
@cassava The third suggestion is indeed a hack. I don't know exactly how PostgreSQL gets the information for reference checking. My guess - simple table or index scans. Maybe with some internal magic to speed it up. – Ihor Romanchenko Jan 16 '13 at 12:23

mlt · Answer 2 · 2020-01-27T22:51:31.843

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.

wildplasser · Answer 3 · 2013-01-16T11:30:18.163

-1

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
    );

edited Jan 16 '13 at 11:30

answered Jan 16 '13 at 11:24

wildplasser

43,142
8
66
109

3

I need the behavior for an indeterminate amount of referencing tables. So `other` is not the only other table! ;-) – cassava Jan 16 '13 at 12:10
If you need the deletes to be instantanious, the reference count+ triggers (Igor's answer) is the only way. If you could postpone the deletes to a periodic clean-up, you could query the catalogs to find the referencing tables and build a (dynamic) query from that. – wildplasser Jan 16 '13 at 13:11

Get all the rows referencing (via foreign keys) a particular row in a table

3 Answers3

Linked