PostgreSQL – How to Delete All Records Not Referenced from Other Tables

postgresql

I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?

I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.

There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.

Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.

Best Answer

I can think of no better way than doing:

DELETE FROM referenced_table AS r
WHERE NOT EXISTS (SELECT 1 FROM referencing_table_1
                  WHERE ref_id = r.id)
  AND NOT EXISTS (SELECT 1 FROM referencing_table_2
                  WHERE ref_id = r.id)
  AND ...;

This is a very unusual requirement.

If you need to do that often, you could even consider denormalizing your database:

add a column reference_count to the referenced table
define triggers that update reference_count whenever rows in the referencing tables are added, deleted or modified

Then you can simply delete the rows with reference_count = 0. Processing will be fast (if all your foreign keys are indexed!!), but you buy that at the price of slowing down all data modifications on all referencing tables.

Related Solutions

Postgresql – Best way of finding rows referencing a given id on PostgreSQL

I suggest your first option, with two improvements and some simplifications.

(
SELECT 1      -- irrelevant what you select here
FROM   client_category_price
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1      -- may be redundant
)
UNION ALL     -- not just UNION

  ...

UNION ALL
(
SELECT 1
FROM   work_order_item
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1
)
LIMIT  1;      -- this one is crucial

Given that all you want to know is

if any of those (table, column) have my row's id there, which would prevent its deletion.

You don't need a full list of violating rows. Stop searching at the first one. All you need to do is add another LIMIT 1 at the end of the query. This way, Postgres skips rest of the query as soon as the first row is found. You probably don't need LIMIT 1 for each SELECT, just the one at the end. Test without, it may produce different query plans.
Use UNION ALL instead of UNION. Faster.
Some other simplifications.

Postgresql – Delete data from multiple related tables

You can use Jailer tool, it will find all rows of child tables that reference to master table.

Example: I have 4 tables : employee, employee_detail, phone_address, relationship. I want to delete employee which name="JOHN". With Jailer, it will find rows in employee_detail & relationship which FK to "JOHN" (by id). And because of phone_address reference to employee_detail, so Jailer will find rows in phone_address.

tb_employee ----(1,n)----tb_employee_detail----(1,n)----tb_phone_address 
            ----(1,n)----tb_relationship

Best Answer

Related Solutions

Postgresql – Best way of finding rows referencing a given id on PostgreSQL

Postgresql – Delete data from multiple related tables

Related Question