Friday, 3 October 2014

A duplicate value cannot be inserted into a unique index - SQL Error

You will typically receive this error when you are attempting to insert a row into a table. A column within your table will be a unique indexed column (Probably a Primary Key), and your new row has a value for this column which already exists within the database, making it a duplicate.

If the problem is your primary key, it's probably set to auto-increment and you have recently dropped some tables or refreshed your database for example. The problem therefore lies with your databases Seed Value. With the auto-increment properly of the primary key, the Seed Value will determine the next ID for your primary key. Chances are this has probably been reset with your recently database refresh. You can solve this issue with the following line of code...

ALTER TABLE tablename ALTER COLUMN columnname IDENTITY (newseedvalue, 1)

If your primary key values end at 150 for example, then you should replace 'newseedvalue' with 151. This will then be the new seed value for the next primary key.

Example (Start PK Identity at 1 for the tblNames table):
ALTER TABLE tblNames ALTER COLUMN ID IDENTITY (1, 1)

No comments: