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?

  • Are all tables InnoDB or MyISAM ??? – RolandoMySQLDBA Sep 12 '12 at 16:16
  • all the tables are InnoDB. – Aditya Kumar Sep 14 '12 at 12:45

The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE 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 DELETEs in application code.

| improve this answer | |

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.