Reputation: 1
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
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