Skip to content
Advertisement

Delete records referenced by other records in different tables

I have a following requirement:

I need to delete records from one table based on the given ID, now this table is referenced by another table and that other table is referenced by yet another table and that last table is also referenced by another table, so I have a chain like this:

table_1 <- table_2 <- table_3 <- table_4

I’m not that experienced with SQL so my solution involves using subqueries to perform this.

I have something like this:

So this would clear records from table_4 which reference targeted records in table_3.

For table_3 I would do something like:

Now I move to table_2:

So in the end it leaves me with possibility to cleanup necessary records from table_1 since it’s not being constrained by anything.

I wanted to ask if this seems like viable solution and if there are better ways to do this?

Advertisement

Answer

Do it with a common table expression that chains the DELETE statements:


If you always do it like that, consider defining the foreign key constraints as on delete cascade, then you only need to delete from table_1 and Postgres will take care of the rest.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement