How can I merge data between 2 non-empty mysql databases without causing primary key and foreign key clashes

Question

How can I migrate data from one database to another non-emoty database without having primary key conflict given that the database structures are identical. This is what I mean: Database A has 100 tables with each table interconnected with other tables using primary and foreign keys. Each table contains lots of data.

Data as B has the same kind of data stored in it with primary and foreign key relationships. The two databases are identical but the data stored in them aren't. I want to merge the two databases with out causing a primary and foreign key clash.

The databases are mysql. What could be the possible solution?

Answer 1

There are several elegant ways to solve this

Offsetting

Dependency: Auto incremented numeric ids

You migrate the first database, see what the maximum id is and add that to all your primary key and foreign key values when you migrate the second database. Not very difficult, but you might want to generate your code instead of fixing by hand if there are many tables. If your database is in BCNF, then this should be possible. If not, then you can change your schema to become BCNF and choose this solution.

Identifying rows by something unique

Dependency: FD in the tables.

You insert select into the target database in such a way that all the records will get the correct foreign key values due to the functional dependency that you are using.

Temporary identifiers

Dependency: Time

You create a column cluster (hopefully of a single column) for each table you have and store the ids of the second table in the new temporary column(s) when you do the migration of the second database and refer that value when you identify the records to point to.

Example schema:

users(id, username, email, password)

profiles(id, user_id, name, description, score)

In the target database you migrate the first database and then change the schema to

users(id, username, email, password, source_id)

profiles(id, user_id, name, description, score, source_id)

When you migrate users from the second database, you insert in such a way that you set source_id in the target database to be the value of users.id of the source database. Then, when you migrate profiles, you also insert in such a way that you set user_id to the users.id value where source_id matches the source id. You also set profiles.source_id to your source profile record's id, so you will be able to match something else to this one.

When you finish with the migration, you can remove the temporary columns.

The real deal

The best solution will probably be a combination of the above.

Answer 2

Foreign key constraints are a mean to keep consistency and integrity of the data.

Of course you could just disable constraints checking and insert all the data of each table, but that ruins the consistency, because you will have duplicate keys, and not be able to track references.

Assuming that it is important for you to keep this integrity after the merge, you would need to find a way to make the primary keys unique, i.e. that for each given table from A a set of primary key has no common elements with the corresponding table from B.

If your primary keys are unique strings (GUID), that is likely not a problem. You can try to disable constraints, insert the data, and enable constraints again. And then verify that the number of rows merged = rows_from_A + rows_from_B.

If you have autoincremented integer primary keys, you could try to adjust one of the databases so that the keys have different ranges, and don't clash with each other. For example, if all the keys in database A are less than 1000000, you could make a new database B2 which is a copy of B, but you add 1000000 to each primary key. Then you can merge A and B2.