System.Data.SqlClient.SqlException. ...
Violation of PRIMARY KEY constraint 'PK_PrimaryKeyName'. Cannot insert duplicate key in object 'dbo.TableName'.
Even when attempting to insert data directly into the table via SQL Management Studio, the same error would occur. The source of the issue was that the identity seed values were out of sync with the actual values in the table (a result of doing inserts with IDENTITY_INSERT ON). The simple fix was to change to output text mode in SQL management studio and run the T-SQL query:
SELECT 'DBCC CHECKIDENT (' + Table_Name + ')' FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE'
Run the output of this query - this corrected all the 'duplicate key' issues I was having after the deploy of the database scripts.
4 comments:
I was just having a headache of a similar kind with Linq and Identity columns. I just should Linq to Sql was broken but of course your post reminded me that I had..ahem...tinkered with reseeding the Identity columns during data loading. Thanks for the reminder. I'm back on track.
Ohh, thank you so much.
I had this error today and was convinced something was wrong with my dbml... Then I read your post and remembered that yes, there were some RESEEDs in the migration scripts I had just run.
Awesome.
I have a question...
How to change to output text mode in SQL management studio?
Cntrl+t
Post a Comment