PostgreSql: get all rows that refer (via foreign keys) to a specific row in a table

Question

It seems so simple, but I could not find the answer to this question.

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

How? One of my approaches to solving this problem (in fact, the only approach so far) includes the following: for each table that references this main table, on UPDATE or DELETE rows, to check the reference row in the main, how many others other lines still refer to the reference line. If it drops to zero, I also delete this line in master.

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

More: I have one main table that many others link to.

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

All other tables have the same format:

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

If one of them is not NULL , they refer to a string in master . If I go over this and try to delete it, I will get an error message because it is already mentioned:

 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 does not take too long to figure it out, even if I have many tables referencing master . How can I find this information without error?

Answer 1

You can do one of the following:

1) Add the reference_count field to the main table. Using triggers in detailed tables increases the reference count when a row with this master_id . Decrease the count when the row is deleted. When reference_count reaches 0, delete the entry.

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

3) Create triggers in each detail table that removes master_id in the 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 vice versa:

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