0

My company is evolving a database schema from several databases, one for each customer, to an unique one. We was facing a lot of problems using separated databases. them we decided to start using a centralized data base.

My concern is about the auto incremented ids from the databases.

for example, we have products: 1, 2 and 3 in one database and other products: 1,2 and 3 in other database (with same ids). Those ids are also used as references for another tables.

How can i join those databases in just one and does not mess with the already inserted Ids?

1 Answer 1

1

Non trivial. And, if you have FOREIGN KEYS, it will be worse than that.

Plan A:

Do this to every table in one of the databases that has a potentially conflicting id:

UPDATE tbl SET foo_id = foo_id + 1000

(Pick the number based on avoiding the MAX(foo_id) in the other database.)

Then proceed to merge the matching tables.

Plan B: This has the advantage of not destroying any tables until you have checked the result.

Create a third database and tables. Write a bunch of SQL statements to carefully copy all the rows from one db, then the other db, to the new db. Be sure to copy tables in the correct order. Hopefully, the lets you create new ids for everything. (I have not thought through the details.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.