MySQL: Can I check if a row's id is used in ANY other table?-CodePudding

I'm trying to implement a cleanup job for our S3 bucket. We represent each object in the S3 bucket as a file entity in our database. Then we have business entities, contact or manager for example which have an image field that link to the file via it's id.

Now I need to check if any of the files is used in any of the other tables. If not, I want to delete it.

The question: How can I check if a row's id is used in any other table without making explicit queries to all the other tables?

I already thought about deleting every file and run into "controlled contstraint errors", which doesn't seem clean though.

In case this is relevant: I use Nest.js with typeorm.

CodePudding user response：

DELETE table_to_clean
FROM table_to_clean
LEFT JOIN (
    SELECT main_id FROM referenced_table_1 
    UNION ALL
    SELECT main_id FROM referenced_table_2
    UNION ALL
    ...
    UNION ALL
    SELECT main_id FROM referenced_table_N
) collection_of_ids ON table_to_clean.id = collection_of_ids.main_id
WHERE collection_of_ids.main_id IS NULL