CODEWITHSUNDEEP
sql-serversql-server-2008constraintsprimary-keyunique-constraint
José María Iglesias
José María Iglesias

Reputation: 1

SQL Server 2008 State:23000,Code:2627,Error:Violation of PRIMARY KEY

I have a database with a table mediainteraction with 10 records: 1 to 10

I have imported data from another table and now it has 20 records: 11 to 20

When system tries to create a new record I received this error:

State:23000,Code:2627,Error:Violation of PRIMARY KEY constraint 'pk120_mediainteraction_pkey'. Cannot insert duplicate key in object 'dbo.mediainteraction'. The duplicate key value is (11).

It seems like if the system is trying to create new record with pkey 11 instead of 21.

Upvotes: 0

Views: 9403

Answers (1)

AdamL
AdamL

Reputation: 13201

The table was probably filled with Fast Load - Keep Identity option, or this clause was used:

SET IDENTITY_INSERT table ON

This caused the table to accept identity values from another table and not creating its own values. So current identity of the table is lower that already existing values, and adding new rows causes this error.

Check current identity with:

DBCC CHECKIDENT ('mediainteraction', NORESEED)

An fix the problem using:

DBCC CHECKIDENT ('mediainteraction')

Or specify own current identity value leaving the gap:

DBCC CHECKIDENT ('mediainteraction', RESEED, 100)

Upvotes: 1

Related Questions