Reseed applying "incorrect" identity values
Published 05 June 2013
After deploying data using SQL Data Compare or SQL Packager, the next time data is inserted into one of the deployed 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 deployment if the Reseed Identity columns project option 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 deployment or at the same time as the data comparison
- The direction of deployment was changed. In that case the databases need to be compared again because the identity value is gathered at registration-time
- There are no differences in the table, at that point SQL Data Compare 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 deploy the current identity, which may be higher than expected. Using TRUNCATE TABLE will roll the identity back to the base seed value.