CODEWITHSUNDEEP
mysql
loganathan
loganathan

Reputation: 6156

How to resolve primary key collision while merging one db with other

I am having my application deployed on two separate regions say US-WEST and EU, both application has its own DB. And now I want to move the EU region DB to US-WEST.

This will lead to primary key collision since both the db has the tables with same primary auto increment id, can anybody give me suggestion to solve this.

Scenario: User Table from DB1(say from US-WEST) has the following entries

ID Name 
1  Rob
2  San
3 Tulip

User Table from DB2(say from EU) has the following entries

ID Name  
1  John
2  Michael
3  Natasha

Upvotes: 3

Views: 5057

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

  • For every one of the two original databases (say db0 and db1):

    • Back up the db.

    • Lock database for use by this script only.

    • For all the tables in the database that have foreign keys defined without ON UPDATE CASCADE, change all these foreign keys constraints with this option.

    • For every table with an auto_increment (or a simple integer) Primary Key, run this (the cascading updates will make the rest):

.

           UPDATE TableX
           SET Pk = 2 * Pk - 0         --- for db0
           ORDER BY Pk DESC

           UPDATE TableX
           SET Pk = 2 * Pk - 1         --- for db1
           ORDER BY Pk DESC

  • Export the tables from each database.

  • Now merge the two databases by simply merging the corresponding tables. All data from db0 will have even ids and all from db1 will have odd ids. No collisions.

  • For tables without auto-incrementing Primary Keys or for tables which may have common rows, the merging should be different, off course.

  • Unlock.

You can read about auto_increment_increment and related system variables that you can change so from this point, the two databases produce different auto incremented ids (one odd ids, the other even ones).

Upvotes: 11

Andreas Rohde
Andreas Rohde

Reputation: 609

I think you have to extend your destination DB with a column for example regionID and edit the primary key settings for this table. Use a Primary key with the two columns ID and regionID. Then import the data from the two tables like this:

Insert into destination_table values(regionID, ID, Name)
select 1,ID, Name from DB1

Insert into destination_table values(regionID, ID, Name)
select 2,ID, Name from DB2

Now, the tricky part. You have to do this for all tables, where you use the ID as a relation. After transferring all data you only have to edit your SQL statements to use regionID and ID combined as key.

Upvotes: 1

juergen d
juergen d

Reputation: 204884

Turn off auto-increment in your destination DB. Then first import data from DB1 and the from DB2. In your importing from DB2 add a constant value that is higher than your hightest id in the first DB. Like this:

insert into destination_table 
    select id + 10000, othercolumns from source_table

After importing the data you can turn on auto-increment again.

EDIT :

If your id column references to other tables then this method will break the relation to these tables.

Upvotes: 1

user319198
user319198

Reputation:

  1. Remove primery key and Turn off auto-increment from id field your destination DB table.

  2. Then first import data from from both DB.

  3. Delete id column from destination table.

  4. Create again id column make that column auto increament primary key.

Upvotes: 0

Related Questions