1

I create 3 database in different place that not connected to the internet.

DB A is Main office Database

DB B is Branch office 1 Database

DB C is Branch office 2 Database

Every evening i need to copy data from transaction table from B & C into A. and copying A data into B & C on next morning to make the stock data sync.

the transaction table consist of 2 tables

1 . transaction-master

| ID | TRX_NUMBER | DATE | CUSTOMER_ID |

2 . transaction-details

| ID | TRANSACTION_MASTER_ID | ITEM_ID | QTY | PRICE | TOTALS |

transaction-details.TRANSACTION_MASTER_ID is foreign key to transaction-master.ID

the problems is transaction-master.ID is auto increment value, so how to copy transaction-master & transaction-details data into A without break transaction-master & transaction-details foreign key value?

Because if on B my table have 1 as transaction-master.ID value, then when i insert B data into A,possible that 1 is already in use and will be change into 2 (because auto increment) and will cause foreign key on transaction-details.TRANSACTION_MASTER_ID will break

What is best practice to solve this issue?

SOLUTION: i got good solution from MySQL channel on IRC, use "auto_increment_offset" and "auto_increment_increment" on MySQL

Set auto_increment_offset A: 1, B: 2, C:3

Set auto_increment_increment 3 on A, B, and C

That way the primary key will never be the same on each branch

frozenmaiden
  • 53
  • 1
  • 6

1 Answers1

0

I can think of two easy ways:

  1. Do not use auto increments. Use, for example, UUIDs. OR:

  2. Add another field to Transaction-master, siteId, and make it part of a compound key. Then, use Transaction_master_ID and siteId as foreign key on transaction-details. See th4 answer to this question to see how to do this: Multiple-column foreign key in MySQL?

Community
  • 1
  • 1
neutrino
  • 2,297
  • 4
  • 20
  • 28
  • Well, point 1 is exactly right, without auto increment no problems appear, but i already build my database and finish the code.. i got another solution that better for my condition, that solution are to use "auto_increment_offset" and "auto_increment_increment" on MySQL – frozenmaiden Aug 07 '13 at 14:00