I have a MySQL database, with one table that has a primary key, f_id
that is referenced in multiple table as a foreign key.
I want to delete all records where f_id
is a specific value from all referenced tables in a single batch.
I am attempting to delete a specific f_id
record in the primary table, however I cannot since there are records present in the referenced tables containing that f_id
value.
How can I delete all related records at once without having to hand-write a query for each and every related table?
Best Answer
The automated way of doing so is by defining foreign keys on your tables, with a
DELETE CASCADE
in your case. This way, aDELETE
on the master table will propagate and delete matching rows from all derived tables.You will have to use
InnoDB
tables for that.I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which case you may try using triggers, see this post for an example. Triggers are bad for performance, mind you.
Those who do not define foreign keys tend to work out all
DELETE
s in application code.