Get all the rows referencing (via foreign keys) a particular row in a table
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
.
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.