1

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the master table, but reference other tables also. The 200 values in the master table are spread out in other 9 tables.

I want to find out if any of the 200 values are present in all the 10 tables and delete them.

Suppose the master table has id = 1 and this record needs to be deleted. Apart from deleting the value in this table, I need to check the remaining 9 tables whether this value is present or not and delete those records also.

For example, if I need to delete the second table, the second table references 3 tables. One is the master table and other two are different tables. That is why I am in a fix.

How can I do this?

Some of the tables have been declared with on delete no action. I am not in a position to change that. Need to find another way without doing that.

Also, when I use foreign keys and delete the other tables, the other tables have been referenced by some other tables also. It is like a chain.

Improve this question
1
  • 2
    You'll need to do DELETE ... USING ... joins, starting at the "ends" of the FK chains, then working your way back up. This could be automated with some PL/PgSQL logic that queries information_schema if there are too many to do manually.
    – Craig Ringer
    Commented Jun 20, 2015 at 0:39

1 Answer 1

Reset to default
1

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.