多表关联链下基于ID删除记录的方案可行性及优化咨询

First off, yes, your nested subquery approach does work—it will correctly delete related records in reverse order (from table_4 up to table_1) as long as your subqueries are written accurately. That said, it has a few notable downsides to keep in mind:

• Performance overhead: Nested subqueries can be less efficient than other methods (like joins) on large datasets, since the database may execute inner subqueries multiple times.
• Repetition & human error: You’re repeating similar subquery logic across multiple DELETE statements, which increases the chance of typos (e.g., mistyping a column name) or missing updates if your schema changes later.
• Manual execution dependency: You have to strictly follow the deletion order (table_4 → table_3 → table_2 → table_1) or you’ll hit foreign key constraint errors.

1. Use ON DELETE CASCADE (Highly Recommended) #

The cleanest and most maintainable solution is to leverage foreign key constraints with cascading deletes. This lets the database handle the entire deletion chain automatically—you only need to delete the target record from table_1, and all related records in table_2, table_3, table_4 get deleted for you.

How to set it up: #

For each foreign key relationship, add the ON DELETE CASCADE clause when defining (or altering) the constraint:

• For table_2’s pk_of_table_1 column:

  ALTER TABLE table_2
  ADD CONSTRAINT fk_table2_table1
  FOREIGN KEY (pk_of_table_1) REFERENCES table_1(id)
  ON DELETE CASCADE;
  

• For table_3’s pk_of_table_2 column:

  ALTER TABLE table_3
  ADD CONSTRAINT fk_table3_table2
  FOREIGN KEY (pk_of_table_2) REFERENCES table_2(id)
  ON DELETE CASCADE;
  

• For table_4’s pk_of_table_3 column:

  ALTER TABLE table_4
  ADD CONSTRAINT fk_table4_table3
  FOREIGN KEY (pk_of_table_3) REFERENCES table_3(id)
  ON DELETE CASCADE;
  

Once these constraints are active, you only need one statement to trigger the entire deletion chain:

DELETE FROM table_1 WHERE id = ?;

2. Use JOINs Instead of Nested Subqueries #

If you can’t modify your table schema (to add cascading constraints), using JOINs in your DELETE statements is a better alternative to nested subqueries. JOINs often have better performance and are easier to read/maintain.

Example DELETE statements with JOINs: #

• Delete from table_4:

  DELETE t4
  FROM table_4 t4
  JOIN table_3 t3 ON t4.pk_of_table_3 = t3.id
  JOIN table_2 t2 ON t3.pk_of_table_2 = t2.id
  JOIN table_1 t1 ON t2.pk_of_table_1 = t1.id
  WHERE t1.id = ?;
  

• Delete from table_3:

  DELETE t3
  FROM table_3 t3
  JOIN table_2 t2 ON t3.pk_of_table_2 = t2.id
  JOIN table_1 t1 ON t2.pk_of_table_1 = t1.id
  WHERE t1.id = ?;
  

• Delete from table_2:

  DELETE t2
  FROM table_2 t2
  JOIN table_1 t1 ON t2.pk_of_table_1 = t1.id
  WHERE t1.id = ?;
  

Then delete the target record from table_1 as usual.

Your original approach is valid for small datasets or one-off tasks, but ON DELETE CASCADE is the best long-term solution—it’s less error-prone, reduces code duplication, and lets the database handle the heavy lifting of maintaining referential integrity. If schema changes aren’t an option, switching to JOIN-based DELETEs will improve both performance and readability.

内容的提问来源于stack exchange，提问作者whatamidoingwithmylife