Identity Key Problem and DBCC CHECKIDENT

 

A colleague today said they received an error like this:

Msg 2627, Level 14, State 1, Procedure SP_name, Line 33
Violation of PRIMARY KEY constraint ‘PK_tableName_log’. Cannot insert duplicate key in object ‘dbo.tableName_log’. The duplicate key value is (19).

I’ve interpreted the above error message as follows:

dbo.tabloAdi_log has a Primary Key Constraint named PK_tabloAdi_log. In the column defined as PK, the value of “19” is tried to be inserted again while its present on this table.

First I found out which is the Primary Key column in this table. Then I looked at whether the IDENTITY property of this column is open. Then I checked the stored procedure that tried to insert a record into this column. Everything seemed right, the stored procedure was not trying to INSERT a value in the corresponding PK field, so the next Identity value had to be generated automatically.

I then checked the Identity value that was automatically generated in this table with the following command:

The result of the above command returned 19. So it’s the same value I got in my error message. SQL Server was trying to create a value of 19 in the PK field in this table, but I was getting an error because the table already had this value.

Then I found the largest value in the table with the following command:

The result of the above command is 13617043.

I haven’t found it yet, but somehow a transaction had changed the SEED of the table and the newly created Identity values continued from 19. That was the cause of the error. With the following command I changed the value of SEED to continue from 13617044.

And our problem has been solved.

You may want to read below articles:

Identitiy Insert On SQL Server”,

How To Set Identity Property to the Current Column in the Table”

Ekrem Önsoy
Author: Ekrem Önsoy

The original article was written in Turkish by Ekrem Önsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.

Leave a Reply

Your email address will not be published. Required fields are marked *