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.Sounds like a nice problem :p
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:
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.|||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.

HELP! Synchronize db and maintain foreign 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...local is ms sql..

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.Hi,

The way i'm using is to create my own table with two columns...

The columns are Old_ID and New_ID

Before syncronization u inserts all elements from the synchronized table there!

After appending ur data into the main table u need to identify using other field /s/ which id corresponds to ur ancient id /with Update action query

The last step is appending the info from the table with foreign keys linked with ur temporary table. Instaed using the old foreign key u need to put the new entry and the information will be at its place!

Hope this helps as conception!

:)

Help! Report Model Project doesn't like my primary key

I'm creating a report model in VS2005 I've created my data source fine and I have selected all the tables I want in the report model data view.

The problem is that for one of the tables it is refusing to acknowledge the promary key. If I try to create the report model it compains that the table doesn't have a primary key.

So I went into SQL Management Studio and checked the table, Lo and behold the primary key is there!!! I tried droping the primary key and recreating it but it still says there is no primary ley on the table.

Any ideas?!?Just did some fiddling and managed to find the problem.

There is a Unique Clustered Index on the table which the problem comes from, with the index it doesn't see the primary key, without it the key suddenly appears

Help with Update from Child table

I have two tables tblInvoice and tblInvoice Line.
Both tables have a column InvoiceID that is Primary / Foreign Key .
tblInvoice has a column TransType char(2) 'SI' or 'SC'
I have now added this column to tblInvoice and want to update the
values with the ones from tblInvoiceLine where the InvoiceID matches
before deleting the column from tblInvoiceLine.
tblInvoiceLine has many records for each invoice but all are the same
transaction type so any will do.
Can sonmeone show how to write this update?
Thanks
Hals_leftThis script should do the work.
update tblInvoice
set TransType = b.TransType
from tblInvoice a join
(select distinct InvoiceID, TransType from tblInvoiceLine) b
on a.invoiceid = b.invoiceid
"hals_left" wrote:

> I have two tables tblInvoice and tblInvoice Line.
> Both tables have a column InvoiceID that is Primary / Foreign Key .
> tblInvoice has a column TransType char(2) 'SI' or 'SC'
> I have now added this column to tblInvoice and want to update the
> values with the ones from tblInvoiceLine where the InvoiceID matches
> before deleting the column from tblInvoiceLine.
> tblInvoiceLine has many records for each invoice but all are the same
> transaction type so any will do.
> Can sonmeone show how to write this update?
> Thanks
> Hals_left
>|||On 11 Nov 2005 06:53:57 -0800, hals_left wrote:

>I have two tables tblInvoice and tblInvoice Line.
>Both tables have a column InvoiceID that is Primary / Foreign Key .
>tblInvoice has a column TransType char(2) 'SI' or 'SC'
>I have now added this column to tblInvoice and want to update the
>values with the ones from tblInvoiceLine where the InvoiceID matches
>before deleting the column from tblInvoiceLine.
>tblInvoiceLine has many records for each invoice but all are the same
>transaction type so any will do.
>Can sonmeone show how to write this update?
>Thanks
>Hals_left
Hi Hals_left,
Absar already posted a solution, but beware - if for some Invoice, the
Invoice lines do have different trnasaction type, it will just pick one
at random, without giving an error or even a warning.
The version below will fail (with an error message) if there are
invoices with more than one transactiontype, allowing you to check and
fix the problem before proceeding:
UPDATE Invoice
SET TransType = (SELECT b.TransType
FROM InvoiceLine AS b
WHERE b.InvoiceID = Invoice.InvoiceID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. If you were polite would we have posted something like:
CREATE TABLE Invoices -- assumes ther is more than one!!
(inv_nbr INTEGER NOT NULL PRIMARY KEY,
trans_type CHAR(2) NOT NULL
CHECK (trans_type IN ('SI', 'SC')),
.);
Never put that silly redundant "tbl-" prefix on names. You did not
know that you sell items and NOT the PHYSICAL lines on an order form.
Newbies often confuse the PAPER FORMS with a logical data elementr like
that.
CREATE TABLE InvoiceDetails
(inv_nbr INTEGER NOT NULL
REFERENCES Invoices(inv_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
upc CHAR(13) NOT NULL
REFERENCES Inventory(upc)
ON UPDATE CASCADE,
item_qty INTEGER NOT NULL
CHECK (item_qty > 0),
.);
So I have an order with two details; one is 'SI' and one is 'SC';
which one goes into the Invoices table? Do not assume that all details
will have the trans_type.
Based on 20+ years of writing SQL standards and cleaning up bad design,
I think your DDL is screwed up beyond repair. You need a full review
and rules for scrubbing the data.