Duplicate Values in Identity Column

Question

I've got a table which has a column named id which is of type Identity. But this column contains duplicate values 1..8 and then again 1..10

How in the world is this possible?

Answer 1

Identity column is not the same as primary key. If you insert some values in id column before setting it to identity then it can generate the same values as you have inserted manually.

Answer 2

Identity is merely a default new value. Uniqueness is enforced by primary key and unique constraints.

Duplicates in an identity column can be explained by:

• The column definition did not contain the identity default at some point in time (like Giorgi says)
• The option SET IDENTITY INSERT TableName ON was enabled at some point in time

To fix this situation, drop and recreate the identity column.

Answer 3

I tested what Giogri says and if you enable the Identity Specification (at least on 2008, probably other versions, too) after the table has rows, the DB will start the numbering at the highest integer value. If you have one row with 100 as the column value, then enable Identity, the next insert will be 101. Even with Identity Seed specified as 1. Wasn't what I would have expected, but its what happened.

In addition to SET IDENTITY INSERT, there's also a reseed command. DBCC CHECKIDENT command which will reset your identity values back to what you specify.

Given that enabling the Identity Specification actually starts at the highest integer in the column, someone probably either used SET IDENTITY_INSERT or did a DBCC CHECKIDENT.

The quickest way to resequence as Andomar says is to drop/recreate the column like this

ALTER TABLE tbl
DROP COLUMN ident_column
GO
alter TABLE tbl
ADD ident_column int IDENTITY

SET IDENTITY_INSERT docs: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx
DBCC CHECKIDENT docs: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx

Answer 4

If the identity is not involved in relationships, yes you can drop and recreate it. If there are relationships the situation is more complex.

First, you need to recreate the dupped id number records invididually and reinsert them into the table and get a new identity for them. You need to identify the child records for the ids which are repeated and then figure out which ones go to which of the two new ids. This is the hardest part and may not even be possible.

Once this is done you update those child records to the new ids. then you drop the old parent records when no child records are there anymore. If you (and by you, I mean the company not necessarily the programmer, sometimes this is something only the users can do) can't identify which parent record a child record goes to, then drop those child records and then drop the old parent records. If you have child records you don't want to drop becasue you need the data for historical reporting of costs or some such thing, then retain one of the old parent records. In this case, I would probably change one of the parent records to say Unknown as the user name or whatever other value you have in the table that also identifies the record and then delete the other duplicated id.

Good luck, data integrity issues like this are hard to fix. Also I would search through your code base for the phrase "set indentity_insert" to make sure no short-sighted programmer is avoiding using the identity field properly. You do not want this problem to re-occur. If you find this code and know who did this to you (source control is a wonderful thing), I would suggest this is the best person to assign to fix any data integrity issues. Go through the pain of fixing a data integrity problem once and you will be a much more careful programmer in the future.