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.