I am trying to perform a simple migration of some data from an old MySQL database to a new MySQL database. The data I transfer is a forum, so there are two tables: threads and posts . I did my best until I came across some complex interdependent external relations.
Database schema (simplified for important parts):
threads (id, title, user_id, created_at, updated_at) posts (id, thread_id, user_id, body, created_at, updated_at)
As you can see, thread_id is the foreign key corresponding to the id thread to which the message belongs. Here lies the problem: the new database already has streams and messages in it, and the primary keys automatically increase . It is not difficult to understand what the problem is: the threads and message identifiers in the two data sets will not coincide / will conflict with each other! If I just populated the message table, they would now correspond to the wrong threads, etc.
How can I solve this by inserting threads / messages from the old dataset into a new dataset without inserting id and relationships? The ways I thought of:
- add a large amount to the old thread / post tags so that they do not conflict with the new
- Surrender and go with my tail between my legs, believing that this is impossible.
Is there an easy way to do this that I am missing? The method of adding a large number does not seem ideal to me. Database Wizards, please apply! The solution will be that the old data is added to the new database, and all messages still refer to the correct streams.
source share