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

  • You mean foreign key relations from the child tables (to the master table)? Commented Oct 13, 2011 at 15:19
  • Yes, Forign key relations from the child tables to the master table.
    – Phanindra
    Commented Oct 14, 2011 at 2:18

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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