How can we combine two databases with identical schemas?

Question

We have two instances of the rails application that each time talk to their own database; we are in the process of converting them into one application with a single database. We have already done parts that should be specific for a particular domain, work correctly; now we just need to combine the databases. We are going to copy data from one instance to another database and correct the identifiers so that they do not overlap. There are many tables with lots of foreign keys. What a good way to do this so that foreign keys still point to the correct row in the new database?

If this is unclear, I am happy to complicate matters with bad ascii art.

Answer 1

Most relational databases allow you to annotate a foreign key, which should be limited in order to keep track of when the primary key in the index table changes. You can set that the foreign key will be “automatically updated” when this happens using ON UPDATE CASCADE . Do this for all foreign keys in both databases, then update all primary keys in both databases and all foreign keys will be automatically converted.

Answer 2

How to update each id column (including foreign keys) so that it is its original time value of 10, then add 1 for the first database and 2 for the second database.

Thus, id 1 becomes 11 on db 1 and 12 on db 2. Since the primary and foreign keys go through the same change, you don’t have to worry about how the records relate, you just make updates using the same formula.

So it will be like

In db 1:

 UPDATE user SET id = id * 10 + 1; UPDATE privilege SET id = id * 10 + 1, user_id = user_id * 10 + 1; 

In db 2:

 UPDATE user SET id = id * 10 + 2; UPDATE privilege SET id = id * 10 + 2, user_id = user_id * 10 + 2;