CODEWITHSUNDEEP
mysqldatabasemerge
Gabe
Gabe

Reputation: 1249

Combine SQL Databases with Conflicting IDs

I have 2 databases that I would like to combine. They both have the same schema. The second database started with the same data as the first, but they diverged on a specific date.

The problem is that the ids for some of the tables continued to auto-increment from the same point so that both tables use the same ids for different data. I should also mention that the ids are used as foreign keys in other tables.

I was thinking that I could just add a value to every id in the second database to remove the conflicts. Is this an acceptable way to do this. If so, what is the best way to do that. If not, what would be a better way.

Upvotes: 0

Views: 2381

Answers (2)

Matthew Flaschen
Matthew Flaschen

Reputation: 284927

Alternatively, double all the ids in one of the databases before combining. The advantage to this is it's pretty obvious. You just have to do it everywhere (including foreign keys), and you can usually tell tables that were omitted since some ids will be odd.

This can be generalized to combining more databases. For instance, for three databases, one should have multiples of three, one should be multiples offset by +1, and the last is is offset by +2.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270707

You can add an offset to all the tables' primary and foreign key values, taking care that

  1. the database is offline and not accepting changes
  2. you do not miss any primary or foreign key fields
  3. you obviously add the same offset everywhere.

Example updates statements:

UPDATE parent_tbl SET primary_key_col = primary_key_col + 5000;
UPDATE child_tbl SET foreign_key_col = foreign_key_col + 5000;
...
...
UPDATE parent_tbl_99 SET primary_key_col = primary_key_col + 5000;
UPDATE child_tbl SET_99 foreign_key_col = foreign_key_col + 5000;

This is an instance where creating your foreign keys with the ON UPDATE CASCADE option would have been very helpful. You would only have had to modify the primary key columns, and changes would propagate to tables referencing them as foreign keys.

FOREIGN KEY (localcol) REFERENCES parent_tbl (parent_col) ON UPDATE CASCADE

Upvotes: 5

Related Questions