MySQL limiting delete

Question

There are multiple instances of one film in the table inventory and I am trying to delete just one with a given title from the table film. This is my query at the moment but it throws back an error:

DELETE FROM inventory
WHERE film_id IN (SELECT film_id
                  FROM film
                  WHERE title = "ACADEMY DINOSAUR")
AND store_id = 1
LIMIT 1

I get this error:

Cannot delete or update a parent row: a foreign key constraint fails

Film table values image

Inventory table values image

Any help would be appreciated, thanks

find the tables with the foreign keys on your data - https://stackoverflow.com/questions/806989/how-to-find-all-tables-that-have-foreign-keys-that-reference-particular-table-co delete that data first — Ctznkane525, Apr 09 '18 at 18:15
Check this link and try to adapt to it https://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails — Kartiksgayatri, Apr 09 '18 at 18:26

score 0 · Accepted Answer · answered Apr 09 '18 at 18:13

The error being produced is not related to the delete or the limit, but a system variable called foreign_key_checks. It can be changed by adding a line before your statement and another after to change it back. Depending on your use, you may want to look into the downfalls of disabling it, i.e. adding data that conflicts with foreign keys, but this will stop that error from happening:

SET foreign_key_checks = 0;
DELETE FROM inventory
WHERE film_id IN (SELECT film_id
                  FROM film
                  WHERE title = "ACADEMY DINOSAUR")
AND store_id = 1
LIMIT 1
SET foreign_key_checks = 1;

MySQL limiting delete

1 Answers1