Entity Framework multi-column Index - Cannot insert duplicate key row in object

Question

Error is occurring when i run my create+seed database script.

Cannot insert duplicate key row in object 'dbo.ResourceCategories' with unique index 'IX_UniqueResCatName'. The duplicate key value is (DSC20, DST20).

It happens on the first row I attempt to seed, but the Database has definitely been dropped and destroyed.

My ResouceCategory.cs file is setup to have a 2 element unique index, as discussed here

[MinLength(1), MaxLength(5)]
[Index("IX_UniqueResCatName", 1, IsUnique = true)]
public string Name { get; set; }

[MaxLength(50)]
[Index("IX_UniqueResCatName", 2, IsUnique = true)]
public string Description { get; set; }

I've auto-generated the Migrations for this this (which runs before seeding), and the relevant portion looks like

CREATE UNIQUE INDEX [IX_UniqueResCatName] 
ON [dbo].[ResourceCategories] ([Name], [Description])

And an excerpt from the seeding SQL (ran automatically from a bash script):

INSERT INTO [dbo].[ResourceCategories] ([Id], [Description], [Name], [IsActive], [CreatedByUser], [CreatedOn], [LastUpdatedByUser], [LastUpdatedOn]) 
VALUES (NEWID(), 'DST20', 'DSC20', 1, 'system', GETDATE(), 'system', GETDATE())

INSERT INTO [dbo].[ResourceCategories] ([Id], [Description], [Name], [IsActive],[CreatedByUser], [CreatedOn], [LastUpdatedByUser], [LastUpdatedOn]) 
VALUES (NEWID(), 'EPT10', 'EPC10', 1, 'system', GETDATE(), 'system', GETDATE())

INSERT INTO [dbo].[ResourceCategories] ([Id], [Description], [Name], [IsActive],[CreatedByUser], [CreatedOn], [LastUpdatedByUser], [LastUpdatedOn]) 
VALUES (NEWID(), 'EPT10', 'EPC11', 1, 'system', GETDATE(), 'system', GETDATE())

INSERT INTO [dbo].[ResourceCategories] ([Id], [Description], [Name], [IsActive], [CreatedByUser], [CreatedOn], [LastUpdatedByUser], [LastUpdatedOn])  
VALUES (NEWID(), 'EPT10', 'EPC12', 1, 'system', GETDATE(), 'system', GETDATE())

I never experienced, this message has another reason, than a duplicate Key. Your 4 lines do not contain duplicates, but if this is just an excerpt, nobody can proof it. And we have to trust you the SQL-Table is Empty before. An empty table - inserting 1 row - reporting a duplicate !?! Just impossible. — Holger, Oct 16 '19 at 22:34

James L. · Answer 1 · 2019-10-17T19:06:19.043

1

The issue was that my Create+Seed script was running the seed twice, and the errors were rightfully occurring in the second run.

I ran it as CreateDatabases.cmd dev localhost SeedData.sql, but it was designed to always run SeedData.sql and optionally run whatever is the 3rd arg. Since I was passing in "SeedData.sql" (and not "SeedData_Stress.sql" for example), it was running twice.

I modified the script to ignore the 3rd arg if it was "SeedData.sql":

set Seed=true
if not '%3' == '' (
    if '%3' == 'SeedData.sql' ( :: This file will run automatically no matter what, so don't re-run it if it's accidentally passed in
        set Seed=false
    ) else (
        set SeedData=%3
    )
) else (
    set Seed=false
)

...

if %Seed% == true (
    echo Adding seed data...
    sqlcmd -S %targetServer% -d nexus -E -b -i "%SeedData%"
) ELSE (
    echo No additional seed data being added...
)

edited Oct 17 '19 at 19:06

answered Oct 16 '19 at 21:10

James L.

12,893
4
49
60

1

This doesn't seem like an answer to the question as you have written it and doesn't help lead someone to an answer should they come across the original question. I would either suggest deleting the question entirely if in the end the issue wasn't relevant or editing the question to include the part that was actually an error. At which point you could note what you did to correct or expand an answer to cover with actual code/reference to the question. – Matt Oct 16 '19 at 22:16
Ok I added more info about the problem+fix. – James L. Oct 17 '19 at 19:06

Entity Framework multi-column Index - Cannot insert duplicate key row in object

1 Answers1