The CREATE UNIQUE INDEX statement terminated because a duplicate key was found EF

Question

I'm building an application using Entity Framework and C#, and in one of my tables, I noticed I named the column with the primary key the same name as the foreign key column i.e. dbo.MembershipTypes table's primary key is on "MembershipTypeId" property, and the dbo.Customers table it has a relationship with has a foreign key on "MembershipTypeId" property i.e. public MembershipType MembershipTypeId { get; set; }).

This caused problems, so I renamed MembershipTypeId back to Id (it was initially Id, but the column wasn't an identity column, and EF refused to make it one even after using [DatabaseGenereated(DatabaseGeneratedOption.Identity)] and [Key] attributes).

I'm using code-first migrations, so when I try reverting the changes using

update-database -TargetMigration:(migration before mistake goes here)

in the package manager console, I get this error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MembershipTypes' and the index name 'PK_dbo.MembershipTypes'. The duplicate key value is (0). Could not create constraint or index. See previous errors.

Here's the migration it gives me the error when I try to revert back to an earlier version of the database:

public partial class RenameIdColumnAndMakeItIdentityInMembershipTypeTable : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes");
        DropPrimaryKey("dbo.MembershipTypes");
        AddColumn("dbo.MembershipTypes", "MembershipTypeId", c => c.Byte(nullable: false, identity: true));
        AddPrimaryKey("dbo.MembershipTypes", "MembershipTypeId");
        AddForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes", "MembershipTypeId", cascadeDelete: true);
        DropColumn("dbo.MembershipTypes", "Id");
    }
    
    public override void Down()
    {
        AddColumn("dbo.MembershipTypes", "Id", c => c.Byte(nullable: false));
        DropForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes");
        DropPrimaryKey("dbo.MembershipTypes");
        DropColumn("dbo.MembershipTypes", "MembershipTypeId");
        AddPrimaryKey("dbo.MembershipTypes", "Id");
        AddForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes", "Id", cascadeDelete: true);
    }
}

Here's the T-SQL code EF used to create the table. I made the table with its properties first then populated it with the reference data in a separate migration.

CREATE TABLE [dbo].[MembershipTypes] 
(
    [SignUpFee]        SMALLINT      NOT NULL,
    [DurationInMonths] TINYINT       NOT NULL,
    [DiscountRate]     TINYINT       NOT NULL,
    [Name]             NVARCHAR(255) DEFAULT ('') NOT NULL,
    [MembershipTypeId] TINYINT IDENTITY (1, 1) NOT NULL,

    CONSTRAINT [PK_dbo.MembershipTypes] 
        PRIMARY KEY CLUSTERED ([MembershipTypeId] ASC)
);

Here's the code used to generate the Customers table:

CREATE TABLE [dbo].[Customers] 
(
    [Id]                       INT            IDENTITY (1, 1) NOT NULL,
    [Name]                     NVARCHAR (255) NOT NULL,
    [IsSubscribedToNewsletter] BIT            DEFAULT ((0)) NOT NULL,
    [MembershipTypeId]         TINYINT        DEFAULT ((0)) NOT NULL,
    [Birthdate]                DATETIME       NULL,

    CONSTRAINT [PK_dbo.Customers] 
        PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.Customers_dbo.MembershipTypes_MembershipTypeId] 
        FOREIGN KEY ([MembershipTypeId]) REFERENCES [dbo].[MembershipTypes] ([MembershipTypeId]) 
            ON DELETE CASCADE
);
GO

CREATE NONCLUSTERED INDEX [IX_MembershipTypeId]
ON [dbo].[Customers]([MembershipTypeId] ASC);

This all came about as I was trying to fix the API to create a new customer using [HttpPost].

Please help me understand why I'm getting this error and how to fix it. Also, kindly help me understand why the duplicate key starts at 0 and not 1.

Thanks in advance for the assistance!

Answer 1

Thanks to Gert Arnold for answering this one in the comments.

The reason I was getting this error is that the Id column in the Down method of my migration was not an identity column.

My understanding is that since the Id column, which often has the primary key in a relational database, cannot be null, 0 is inserted there because this is the default value for a byte or integer types.

In my Up method, I made Id an identity column, but since the default value of 0 was already present, this created a conflict because the database is now generating a value for Id starting with 0.

This can't work because an Id column is unique and can't have duplicate values. Even if, for example, you had 5 customers in your database and deleted customer 4, no other customer will have an Id of 4.

Hope this helps someone in the future!