CODEWITHSUNDEEP
mysqlsql
Jatin Gupta
Jatin Gupta

Reputation: 927

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:

Upvotes: 0

Views: 327

Answers (1)

trincot
trincot

Reputation: 350477

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

Upvotes: 1

Related Questions