Reseed applying "incorrect" identity values

Category: Troubleshooting & error messages
Date: 22 Mar 2009
Product: SQL Packager
After synchronizing data using SQL Data Compare or Packager, the next time data is inserted into one of the synchronized tables, an error may result claiming that the automatically-generated value violates a primary key or unique constraint.

SQL Data Compare will "reseed" identity values automatically after synchronization if the "Reseed Identity columns" option in the "options" panel of the project is selected. It does this by gathering the current identity value from the "source" table and applying it to the "target" table. In this way, any identity columns with a primary key constraint will continue generating valid identity values.

There are a few circumstances, however, where the current identity value in the target database will become or remain invalid:

  · A data update was taking place before the synchronization or at the same time as the data comparison
  · The direction of synchronization was changed--in that case the databases need to be recompared because the identity value is gathered at registration-time
  · There are no differences in the table, at that point SDC does not reseed and the identity values remain different
  · All records were deleted from the source table using a DELETE query. In this case the identity is not rolled backwards and SQL Data Compare will synchronize the current identity, which may be higher than expected. Using TRUNCATE TABLE will roll the identity back to the base seed value.

Document ID: KB200903000371 Keywords: SQL, Data, Compare,reseed,identity,violation

Was this article helpful?

Search support