title: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found EF
body: <p>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. <code>dbo.MembershipTypes</code> table's primary key is on &quot;MembershipTypeId&quot; property, and the <code>dbo.Customers</code> table it has a relationship with has a foreign key on &quot;MembershipTypeId&quot; property i.e. <code>public MembershipType MembershipTypeId { get; set; })</code>.</p>
  <p>This caused problems, so I renamed <code>MembershipTypeId</code> back to <code>Id</code> (it was initially <code>Id</code>, but the column wasn't an identity column, and EF refused to make it one even after using <code>[DatabaseGenereated(DatabaseGeneratedOption.Identity)]</code> and <code>[Key]</code> attributes).</p>
  <p>I'm using code-first migrations, so when I try reverting the changes using</p>
  <pre><code>update-database -TargetMigration:(migration before mistake goes here)
  </code></pre>
  <p>in the package manager console, I get this error:</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>Here's the migration it gives me the error when I try to revert back to an earlier version of the database:</p>
  <pre><code>public partial class RenameIdColumnAndMakeItIdentityInMembershipTypeTable : DbMigration
  {
      public override void Up()
      {
          DropForeignKey(&quot;dbo.Customers&quot;, &quot;MembershipTypeId&quot;, &quot;dbo.MembershipTypes&quot;);
          DropPrimaryKey(&quot;dbo.MembershipTypes&quot;);
          AddColumn(&quot;dbo.MembershipTypes&quot;, &quot;MembershipTypeId&quot;, c =&gt; c.Byte(nullable: false, identity: true));
          AddPrimaryKey(&quot;dbo.MembershipTypes&quot;, &quot;MembershipTypeId&quot;);
          AddForeignKey(&quot;dbo.Customers&quot;, &quot;MembershipTypeId&quot;, &quot;dbo.MembershipTypes&quot;, &quot;MembershipTypeId&quot;, cascadeDelete: true);
          DropColumn(&quot;dbo.MembershipTypes&quot;, &quot;Id&quot;);
      }
      
      public override void Down()
      {
          AddColumn(&quot;dbo.MembershipTypes&quot;, &quot;Id&quot;, c =&gt; c.Byte(nullable: false));
          DropForeignKey(&quot;dbo.Customers&quot;, &quot;MembershipTypeId&quot;, &quot;dbo.MembershipTypes&quot;);
          DropPrimaryKey(&quot;dbo.MembershipTypes&quot;);
          DropColumn(&quot;dbo.MembershipTypes&quot;, &quot;MembershipTypeId&quot;);
          AddPrimaryKey(&quot;dbo.MembershipTypes&quot;, &quot;Id&quot;);
          AddForeignKey(&quot;dbo.Customers&quot;, &quot;MembershipTypeId&quot;, &quot;dbo.MembershipTypes&quot;, &quot;Id&quot;, cascadeDelete: true);
      }
  }
  </code></pre>
  <p>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.</p>
  <pre><code>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)
  );
  </code></pre>
  <p>Here's the code used to generate the Customers table:</p>
  <pre><code>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);
  </code></pre>
  <p>This all came about as I was trying to fix the API to create a new customer using <code>[HttpPost]</code>.</p>
  <p>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.</p>
  <p>Thanks in advance for the assistance!</p>