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.

My question is 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.

How can I do this?

| improve this question | |
  • 3
    Why can FOREIGN KEYS not do the job for you (with ON DELETE CASCADE)? – Vérace Jun 20 '15 at 0:06
  • Some of the tables have been declared with on delete no action. – ijaz ahamed Jun 20 '15 at 0:12
  • 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. For eg., If I need to delete thw 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.' – ijaz ahamed Jun 20 '15 at 0:12
  • 1
    Can't you change that? Maybe a silly response, since many posters here can't change their DDL, but I'm just pushing ideas out there :-). My word(s) of advice here would be to get the chain of DDL-->> DML straight, and then you can make your system sing. Otherwise, you'll always be firefighting with ad-hoc solutions. – Vérace Jun 20 '15 at 0:14
  • 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 Jun 20 '15 at 0:39
0

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, privacy policy and cookie policy

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