Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
yes, it worked for me...thnx a lot. –  Aditya Kumar Sep 14 '12 at 12:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.