2

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.

  • I got lost reading the Question. Could you improve on the wording? – Rick James Feb 3 '19 at 16:39
  • Do you mean some sort of event code that determines all the tables which reference images and after that, then deletes the images which are orphaned? – Vérace Feb 3 '19 at 20:46
  • @RickJames. I can write a query using joins, but I need to specify which tables to join. I am asking for a query which auto join the required tables. – Daniel G Feb 5 '19 at 18:08
0

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; 
| improve this answer | |
  • For deleting, this answer, with multiple LEFT JOINS and IS NULL criteria. – danblack Feb 3 '19 at 23:08
  • 2
    The outer needs to be SELECT SUM(...) FROM... – Rick James Feb 5 '19 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.