Search code examples
c#sql-serverlinq-to-sqlidentity-insert

SQL Server gives "duplicate key" error for a non-key property?

I have a DB table products with 5 columns. The Id is the primary key. In SQL Server Management Studio, you can clearly see this here:

screenshot 1

I have a product with Id = 69 and Name = "Lawn Darts" . I am trying to insert a new product with Id = 420 and Name = "Lawn Darts". I am trying to use identity insert so that I can specify the Id for products inserted. So the names are the same but the Id is different. This should be no problem, right?

I am using LINQ --> SQL to insert in a C# console app. When I try to insert something with the same Name but a different Id, I get the following error message:

Cannot insert duplicate key row in object 'dbo.Products' with unique index 'IX_Name'. The duplicate key value is (Lawn Darts).

Why, if it is a non-key?

Solution

  • Well, this was much simpler than I was making it. Thanks to @rook for pointers.

    Even though the Name column was not the primary key, it was specified that it was a "unique index". I was looking in the wrong settings in SQL Server Management Studio, so I missed it. I was looking in the "properties". What I needed to do was right-click on the "name" column and select the "Indexes/Keys..." option. This brings up a window where I can turn the attribute Is Unique to "No".

    screenshot

    Alternatively, since I am using code-first migrations, I can remove the data-annotation from the property where it is defined in C# and proceed to update the DB via this route.

    Before:

    [Index(IsUnique = true)]
[MaxLength(255)]
public string Name { get; set; }

    After:

    [MaxLength(255)]
public string Name { get; set; }