Tuesday 8 December 2009

Reseed an SQL Table Identity Column

If you get an unexpected error like this...

Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'Table_Name'.

You look at your table and the primary key and realise that the primary key that was complianing was a indentity column then you think well isn't that a bit odd. The point of an identity column is that you insert new rows in a database and it generates a primarykey value for you, usually a unqiue integer value.

Sometimes though for some mystic unknown reason (one that I can't be bothered to find out the real cause for) the identity value that is created is invalid. So whats the solution?

DBCC CHECKIDENT


You can use this SQL command to check what the current status of an identity column on a table is. If you run the below command, it will tell you the current identify value, i.e. the next value that would be generated as a unquire identity value, and it will tell you the current column value, ie. the current highest unqiue identity value used in your database.
DBCC CHECKIDENT ("Table Name", NORESEED);

Now if it returns something like this then you may have a problem.


Checking identity information: current identity value '10', current column value '17'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


If the current column value is higher than the current identity value then the problem may be that you are trying to insert a new row and the identity value being inserted automatically is '10' when the rows have already been created for identity values up to '17'.

To fix this just run this command to reset the identity value.
DBCC CHECKIDENT ("Table Name", RESEED);

Then run the previous command again and you should see this result.

Checking identity information: current identity value '17', current column value '17'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Sorted.

No comments: