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 master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table.

Foreign key relations are On-Delete Cascade and On-Update No-Action.

My doubt is when any update happens to the master table, will all child tables get checked and then the update occurs, or update will independently happen on master table.

I want to know how update/delete will happen when there are foreign key relations to child tables.

  • MySQL version is 5.0.24
  • OS is Windows

I wanted to know, when any update happens, will the update query cross verify all child tables and then update the master table, so that will the update query will take time to update master table compared to update on normal table which is not having any foreign key relations

share|improve this question
    
You mean foreign key relations from the child tables (to the master table)? –  Nick Chammas Oct 13 '11 at 15:19
    
Yes, Forign key relations from the child tables to the master table. –  Phanindra Oct 14 '11 at 2:18
add comment

1 Answer

  • ON DELETE CASCADE: When you delete rows in the master table referenced by other tables, all those child tables will also delete their rows that reference the deleted master data.
  • ON UPDATE NO ACTION: If you try to update a field in the master table referenced by others, MySQL will prevent you from doing that. NO ACTION and RESTRICT are synonyms in MySQL.

So to answer your updated question, MySQL will prevent you from updating a field in the master table that is referenced by any child tables because you specified NO ACTION. If you are updating any other field in the master table it will take the same amount of time as an update on a table without foreign keys.

share|improve this answer
add comment

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.