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:

DELETE FROM table_4
     WHERE pk_of_table_3 IN 
               (SELECT id
                FROM table_3
                WHERE pk_of_table_2 IN 
                          (SELECT id FROM table 2 WHERE pk_of_table_1 = ?
                          )
               )

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

For table_3 I would do something like:

DELETE FROM table_3
    WHERE pk_of_table_2 IN 
              (SELECT id FROM table_2 WHERE pk_of_table_1 = ?)

Now I move to table_2:

DELETE FROM table_2 WHERE pk_of_table_1 = 5;

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?

Answer

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

with delete_t1 as (
  delete from table_1
  where pk = 42
  returning pk
), delete_t2 as (
  delete from table_2
  where pk_of_table_1 in (select pk from delete_t1)
  returning pk
), delete_t3 as (
  delete from table_3
  where pk_of_table_2 in (select pk from delete_t2)
  returning pk
)
delete from table_4
where pk_of_table_3 in (select pk from delete_t3);

---

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.