Show example row when a DELETE fails due to a foreign key constraint-CodePudding

I am trying to delete some old historic data from a MySQL database with multiple tables. There is a "volumes" table, and a "metadata" table. Of course, you can't have metadata for a volume that does not exist, so there is a foreign key constraint.

This is a query I'm trying to do:

DELETE FROM volumes 
       WHERE deleted > 0 AND 
             deleted_at IS NOT NULL AND 
             deleted_at < '2020-01-01T00:00:00' AND 
             status = 'deleted' AND 
             attach_status = 'detached' 
       LIMIT 5000

This results in the following error (reformatted for readability):

Integrity error: 'DELETE FROM volumes WHERE deleted > 0 AND deleted_at IS NOT NULL AND deleted_at < '2020-01-01T00:00:00' AND status = 'deleted' AND attach_status = 'detached' LIMIT 5000' 
(1451, 'Cannot delete or update a parent row: 
        a foreign key constraint fails 
        (`volume_glance_metadata`, 
         CONSTRAINT `volume_glance_metadata_ibfk_1` FOREIGN KEY (`volume_id`) REFERENCES 
                                                                `volumes` (`id`))')

All answers I find if I try to search for this are answers to different questions, like "how can I delete this anyway" or "how can I remove the constraint"? That's not what I'm interested in though.

What I would like to know is: How can I find an example of a row from the volumes table that the DELETE query tried to delete, but cannot be deleted, because there is still a row in the volume_glance_metadata table that has the corresponding id (volume_glance_metadata.volume_id = volumes.id)? Or in other words: give me an example that makes the deletion fail.

I presume that there is at least one row that cannot be deleted; or maybe no row exists that can be deleted. It seems a bit disappointing that MySQL doesn't do its best to find other rows to delete, if there are some that would fail.

CodePudding user response：

Just do a SELECT with equivalent conditions on rows in volumes, and JOIN those rows to the dependent table metadata.

SELECT m.*
FROM volumes AS v
INNER JOIN metadata AS m ON m.volume_id = v.id
WHERE v.deleted > 0 
 AND v.deleted_at IS NOT NULL 
 AND v.deleted_at < '2020-01-01T00:00:00' 
 AND v.status = 'deleted' 
 AND v.attach_status = 'detached'

The join is an inner join, not an outer join, so it naturally returns only matching rows.

If this join query returns no rows in its result, then metadata contains no rows that would block your delete.