issues on: primary key is the default comparison key

jamest85

Hi:

We have a Dev and Live database, most of tables have primary key (auto number, increase 1 each time adding a new record).

Since the "Comparison", by default, is based on the primary key, that means each paired table (between Dev and Live) mush have same PK value for the same row record.

That will bring up a problem: if we add a new row in a Dev table (PK value, for example, is 100), and delete it for some reason, and then add it again, the PK value will be (101, the 100 is gone). if we synchronize and insert to Live table, the PK value in Live table will be 100.

And next time "compare", 2 rows in the tables will be treated as different data because the PK values are different, but in fact they are the same.

Do you have any suggestion, how to get around this, add extra column name to compare?

Thanks.

Eddie D

Thank you for your post into the forum.

You are correct SQL Data Compare will use the Primary Key as an automatic comparison key.

To match rows in the two data sources, SQL Data Compare requires a comparison key for each table or view. SQL Data Compare automatically selects a comparison key when:

tables contain a matching primary key, unique index, or unique constraint views contain a matching unique clustered index

To get around the problem you have described, you could try setting a manual comparison key as follows:

Edit the project and select the 'tables & views' tab. Select the table, so that becomes highlighted in yellow. Under the comparison key column, the Primary key will be listed. Click on the Comparison key name, to open open a dialog box. Change the selected comparison key to custom. Select one or more columns to become the custom comparison key and click the close button. Now click on the Compare Now button.

I hope the above resolves your particular issue.

Many Thanks
Eddie