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:
|
1 |
SELECT IDENT_CURRENT('dbo.tableName_log') |
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:
|
1 |
SELECT MAX(id) FROM dbo.tableName_log |
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.
|
1 |
DBCC CHECKIDENT (dbo.tableName_log, reseed, 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”
