If you declare a column as
Identity = true, then SQL will handle the values and will assign them sequentially so that you cannot get repeats. If you try at any time to set the value of that field via an INSERT or UPDATE operation, SQL will throw that exception.
The most common mistake is to try and do an "anonymous" INSERT operation:
INSERT INTO MyTable VALUES (1, 2, 3)
Where the first column in the table is the ID column, declared as identity. SQL tries to assign such inserts in numerical column order, and so tries to write the first value into the Identity field. USe names columns at all times and you won't get the problem:
INSERT INTO MyTable (ProdCount, ItemNo, Bananas) VALUES (1, 2, 3)
Submit an article or tip