How to map data between tables when they are connected by foreign key in mysql?

I have a table for resources which contains resources like case studies, whitepapers, webinars etc.

It has one to many relationship with another table case_study_blocks. So there is a foreign key caseStudyId in the case_study_blocks table, which points to case studies in the resources table.

I want to move all the case studies to a separate table case_studies, but to do that I’ll also have to update the foreign key reference in the case_study_blocks table and make all the records there point to the newly generated unique ids in case_study table.

What is the correct way to migrate the complete data while preserving the relationship?

One way I can think of:

• Drop foreign key constraint on case_study_blocks
• Add new foreign key constraint on the caseStudyId column, pointing to the id column in case_studies table
• But now how do I map the existing case_study_blocks correctly to the new IDs in case_studies table?

Answer

Perform the steps as follows:

• Copy the relevant data (related to case studies) to the new case_studies table, including the original ID value — which could be called oldCaseStudyId — so that the case_studies table will have the newly generated ID (caseStudyId), and the original ID in a separate oldCaseStudyId column. The latter can be dropped when all is done.

• Drop the existing foreign key constraint on case_study_blocks.caseStudyId

• Perform the update of the caseStudyId values by the mapping that is now available in case_studies (it has both the old and new ID values). The SQL statement could look something like this:

  update case_study_blocks
  inner join case_studies on case_studies.oldCaseStudyId = case_study_blocks.caseStudyId
  set case_study_blocks.caseStudyId = case_studies.caseStudyId;  
  

• Create the replacing foreign key constraint on case_study_blocks.caseStudyId

• Delete the original rows from resources that relate to case studies

• Drop the column case_study_blocks.oldCaseStudyId