Will a row update check all child tables which have foreign key relationships in InnoDB?

Question

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

Answer 1

• 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.