After no row deletion, but after getting the 60 existing rows by local subscription to replication from another SQL Server instance, I’m inserting new rows with:
INSERT INTO [Business].[dbo].[ImagesTable] (Filename, Title, Price, PriceString, Category, CategoryRank)
VALUES ('vegan1.jpg', 'vegan1', 380000, '380,000', 'delices vegan', 0)
But this will fail since the ID will start over somewhere (9 in this case), instead of starting at the last ID used +1, ie., 61 in my case.
System.Data.SqlClient.SqlException: ‘Violation of PRIMARY KEY constraint ‘PK_ImagesTable’. Cannot insert duplicate key in object ‘dbo.ImagesTable’. The duplicate key value is (9).
My table is set to have autoincrement, therefore I should not have to use explicit IDENT_CURRENT('ImagesTable'). Why is this happening ?
Furthermore and despite this, when I try to insert explicitly ID I have:
Cannot insert explicit value for identity column in table ‘ImagesTable’ when IDENTITY_INSERT is set to OFF.
What is the recommended way to add new rows? How can I insert new rows with the ID being automatically set?
If the presence of DB replication is not propagating the Identity seed, how should I deal with this so I don’t have to manage duplicate key errors (I’m certain that I’m not inserting duplicates)?
Advertisement
Answer
You need to segment your IDENTITY column ranges in bidirectional replication.