Consolidate multiple MySQL databases into one preserving referential integrity

views:

answers:

+2 Q:

Consolidate multiple MySQL databases into one preserving referential integrity

I'm wanting to consolidate numerous MySQL databases, all with the same schema definition, into a single database. If I have a dump file from each database, how will I import them all into the same database without their primary keys and foreign keys clashing?

Is there a fairly simple way to do this, or will I need to write some custom code that understands the data and creates a consolidated set of records 'manually'?

Either write custom code, or figure out the maximum primary key value in each and consolidate the files, but increase the primary keys in each database so that they don't clash. That's assuming you don't need to remove any duplicates that might exist, which would be a manual process.

Stephen Orr 2010-09-02 12:05:14

In my case I am fortunate that all my foreign keys adhere to a naming convention of 'table-name'_id. So a FK for account is account_id. What I'm working on right now, is to parse each data dump file of SQL statements, and add the existing max for each table id to that paricular file. So if I have:install into profile(id, name, description, account_id) values (1, 'foo', 'bar', 1);if the current max(account.id) is 5 then i add 5 to every account_id i encounter in the file currently being parses. Hope that all makes sense. I'll respond back here as to how this approach works out.

jondow 2010-09-02 14:18:26

Yep, that makes sense and is kind of what I was suggesting - I just think you said it better!

Stephen Orr 2010-09-02 15:19:03

Hehe, thanks Stephen. Your comment definitely clued me into what to do, and I'd actually overlooked the issue of duplicates as I do have some tables that are effectively 'constants'. But I've given that some thought and I can do some post-op work to sift those out - again, as you say 'manually'.

jondow 2010-09-02 15:36:22

"In my case I am fortunate that all my foreign keys adhere to a naming convention of 'table-name'_id." <- I love it. That's my standard too and it makes automation easier... the only problem is if you have a "role-playing" fk. Say you have Person_T table and a child table that needs a Mother_ID and Father_ID to both point the the Person_ID... only one FK can be Person_ID... the other will have to deviate.

Stephanie Page 2010-09-15 18:12:54

Wait.... why would you do the addition in the file instead of the database itself? If you add 1,000,000 to every column in a PK or FK, everything will still be related... then next db add 2M, and 3M and so on... do it in the db and then export.

Stephanie Page 2010-09-15 18:58:07

Very good point - my only thought against that would be that it would be harder to correct if you made a mistake - if you're doing a global search and replace in the file for a specific value, at least you have a method to undo your changes, without necessarily needing to have a full DB backup first.

Stephen Orr 2010-09-16 08:01:38

ansaurus

tags:

views:

answers:

Consolidate multiple MySQL databases into one preserving referential integrity

related questions