I’ve written this blog as much to help others as I have to help myself find this solution in future.
We’re currently migrating our back-end DB from MySQL to Azure SQL. To get things started we used the Microsoft SQL Server Migration Assistant for MySQL (catchy name, eh?). However, I’ve since come across a small problem which I’ve now run into twice, and I’m sure it must exist, as yet un-found, in other migrated tables.
The problem is that the identity value for an ID/key field is not populated with the expected value. This then results in the following exception:
Cannot insert duplicate key in object ‘YOUR-TABLE-NAME‘.
Upon checking in SQL Server, the Identity Seed value appears correctly, but when you attempt an insert you’ll be told that a much lower number (1 or close to 1) is a duplicate value.
The reason for this has something to do with Identity Seed showing the value when that table was created, not necessarily the next value it’s about to use.
You can find the current identity value of a table by running
SELECT IDENT_CURRENT('YOUR-TABLE-NAME')
And to take this one stage further, you can find the next value which will be inserted (because the increment value may not be 1) by running
SELECT IDENT_CURRENT('YOUR-TABLE-NAME') + IDENT_INCR('YOUR-TABLE-NAME')
In our case, IDENT_CURRENT value was 1, even though the Identity Seed value in the table’s designer said 719102.
The fix is simple:
DECLARE @NEWSEED AS INT; -- this should match the type for your ID/key field
SET @NEWSEED = (SELECT MAX([YOUR-ID-FIELD-NAME]) FROM [YOUR-TABLE-NAME]);
IF (@NEWSEED <> NULL) BEGIN
DBCC CHECKIDENT ('YOUR-TABLE-NAME', RESEED, @NEWSEED);
END ELSE BEGIN
DBCC CHECKIDENT ('YOUR-TABLE-NAME', RESEED, 1);
END
Now, to be clear, the above script finds the current maximum value in the specified ID field, then uses this as the new Identity Seed value. However, if there is no maximum value (i.e. the table contains no records) then the seed value is set to 1. If either of these values isn’t the desired identity seed value then set the @NEWSEED parameter as you wish.
Furthermore, the MSDN page documenting DBCC CHECKIDENT carries an explanation of how this value will be used:
Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
That page also has a couple of warnings, so please consult that documentation before blindly running that CHECKIDENT script.