Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    1

    Unanswered: HELP! Synchronize db and maintain foriegn key relations?

    I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.

    Example tables layout:
    Items table has auto-increment primary key 'id'
    TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

    Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
    8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

    locally the following products are added:

    11001 short sleeve t---gets added with primary key in 'item' table 'id' of '7'

    11002 long sleeve t----gets added with primary key in 'item' table 'id' '8'

    remotely the following products are added:

    21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

    31001 overalls---gets added with primary key in 'item' table 'id' '8'

    remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

    product with "item_id" of '7' is "short sleeve t".

    9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

    whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

    I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Sounds like a nice problem
    Never had this one but it's fun to think about it, so this is what I came up with:

    Use different ranges... Set the IDENTITY (or AUTO_INCREMENT) on MySQL on a very high number, one you won't expect to reach in this product life cycle. Let say 10000000.

    When synchronizing set the IDENTITY_INSERT ON, insert the records from the other server. Set IDENTITY_INSERT OFF, reseed to the lower value and continue.

    Example:
    Code:
    CREATE TABLE tst (c1 INT IDENTITY, c2 INT)
    
    INSERT tst(c2) VALUES (1)
    INSERT tst(c2) VALUES (2)
    INSERT tst(c2) VALUES (3)
    INSERT tst(c2) VALUES (4)
    
    SET IDENTITY_INSERT tst ON
    
    INSERT tst(c1, c2) VALUES (10000000, 5)
    INSERT tst(c1, c2) VALUES (10000001, 6)
    INSERT tst(c1, c2) VALUES (10000002, 7)
    
    SET IDENTITY_INSERT tst OFF
    
    DECLARE @i INT
    SELECT @i = MAX(c1) FROM tst WHERE c1 < 10000000
    DBCC CHECKIDENT ('tst', RESEED, @i)
    
    INSERT tst(c2) VALUES (8)
    INSERT tst(c2) VALUES (9)
    INSERT tst(c2) VALUES (10)
    
    SELECT * FROM tst
    
    DROP TABLE tst
    Ofcourse this could be useless in your case, 'cause what happens when someone is inserting while you're synchronizing!? But maybe it's a start.

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    or else u can use another set of tables for remote data and use views to combine both local & remote data. u might be needing an additional flag field in the view to identify the source.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •