1

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?

AlbatrossCafe
  • 1,710
  • 6
  • 26
  • 49
  • You definitely have a unique index (IX_Name) on your Name column. That what the error is. Open Keys folder for your table in SSMS and see there. The second error tells you that you can't insert your own values in the identity column. To do that you need to enable this: `SET IDENTITY_INSERT TableName ON` and of course set it to `OFF` when you're done. – rook Sep 13 '16 at 01:23
  • @rook yep, you are right. I was just looking into the wrong settings. I had to right click on the column and click `Indexes/Keys...` instead of just viewing the regular column properties. In the C# code where `product` is defined it clearly has the `IsUnique` annotation as well. Should have looked their first... – AlbatrossCafe Sep 13 '16 at 01:36

1 Answers1

3

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; }
AlbatrossCafe
  • 1,710
  • 6
  • 26
  • 49