How can we merge two databases with identical schemas?

We have two instances of a rails application which each talk to their own database; we're in the process of converting them to a single application with a single database. We've already made the parts of it which need to be specific to a particular domain work correctly; now we just need to merge the databases. We're going to copy the data from one instance into the other's database, and fix up the IDs so they don't o开发者_Python百科verlap. There are a lot of tables with a lot of foreign keys. What's a good way of doing this, such that the foreign keys still point to the correct row in the new database?

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

---

Most relational databases let you annotate a foreign key to be constrained to watch for when the primary key in the pointed-to table changes. You can set the foreign key to be "auto-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.

---

How about updating every id column (including the foreign keys) to be its original value times 10, then add 1 for the first database and 2 for the second database.

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

So it would go something like

On db 1:

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

On db 2:

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