Query to delete all rows which are not referenced in any other tables

Question

Let's say i have a table called images. Images table is referenced in many other tables (movies, books, tv_series) through a foreign key.

My question is if it is possible to write a query which will delete all orphaned rows without specifying exact tables (eg JOIN movies ON ...). This is important because if new table which references images will be introduced it will make the query outdated. If the query will be executed without changes it could delete images which are used in new table.

dbdemon · Accepted Answer · 2019-02-05T19:21:00.073

I don't think you can write a single query, but you can write a query to get the list of tables that refer to the images table, and then you can do a second task which deletes the rows from images which are not referred to in any of the tables in the list.

List of tables that refer to the images table:

SELECT rf.TABLE_NAME, 
kcu.COLUMN_NAME, 
kcu.REFERENCED_COLUMN_NAME 
FROM information_schema.REFERENTIAL_CONSTRAINTS rf 
  INNER JOIN information_schema.KEY_COLUMN_USAGE kcu ON 
    kcu.CONSTRAINT_NAME = rf.CONSTRAINT_NAME AND
    kcu.CONSTRAINT_SCHEMA=rf.CONSTRAINT_SCHEMA 
WHERE rf.REFERENCED_TABLE_NAME = 'images' AND 
  rf.CONSTRAINT_SCHEMA = 'your_database';

Delete all rows in images not referred to by those tables:

For this part, perhaps it's better to do a loop and look at one and one row in images. Assume '$row_i_val' is the PK column value of the current row you're looking at:

SELECT sum(q.c) FROM 
(
  SELECT count(*) as c FROM $table1 WHERE $table1_col = $row_i_val
  UNION ALL 
  SELECT count(*) as c FROM $table2 WHERE $table2_col = $row_i_val
  UNION ALL 
  SELECT count(*) as c FROM $table3 WHERE $table3_col = $row_i_val
  UNION ALL ...
) q

If this sum is 0, then you can delete it:

DELETE FROM images WHERE pk_col = $row_i_val;

Query to delete all rows which are not referenced in any other tables

1 Answers1