I am getting the error - The operation failed because an index or statistics with name 'IX_address_unique' already exists on table 'dbo.Addresses'. The index in question is a composite index that I created with code first EF version 6. Here is the code in the DbContext class -
// create unique address composite index based on properties
// address1 property
modelBuilder.Entity<Address>().Property(t => t.address1)
.HasMaxLength(250)
.IsRequired()
.HasUniqueIndexAnnotation("IX_address_unique", 0);
// city property
modelBuilder.Entity<Address>().Property(t => t.city)
.HasMaxLength(50)
.IsRequired()
.HasUniqueIndexAnnotation("IX_address_unique", 1);
// state property
modelBuilder.Entity<Address>().Property(t => t.state)
.HasMaxLength(50)
.IsRequired()
.HasUniqueIndexAnnotation("IX_address_unique", 2);
// postal code property
modelBuilder.Entity<Address>().Property(t => t.postal_code)
.HasMaxLength(20)
.IsRequired()
.HasUniqueIndexAnnotation("IX_address_unique", 3);
This creates the following DbMigration when you run the command Add-Migration CreateUniqueIndexAddressesTable.
public partial class CreateUniqueIndexAddressesTable : DbMigration
{
public override void Up()
{
AlterColumn("dbo.Addresses", "address1", c => c.String(nullable: false, maxLength: 250));
AlterColumn("dbo.Addresses", "address2", c => c.String(maxLength: 250));
AlterColumn("dbo.Addresses", "city", c => c.String(nullable: false, maxLength: 50));
AlterColumn("dbo.Addresses", "state", c => c.String(nullable: false, maxLength: 50));
AlterColumn("dbo.Addresses", "postal_code", c => c.String(nullable: false, maxLength: 20));
CreateIndex("dbo.Addresses", new[] { "address1", "city", "state", "postal_code" }, unique: true, name: "IX_address_unique");
}
public override void Down()
{
DropIndex("dbo.Addresses", "IX_address_unique");
AlterColumn("dbo.Addresses", "postal_code", c => c.String());
AlterColumn("dbo.Addresses", "state", c => c.String());
AlterColumn("dbo.Addresses", "city", c => c.String());
AlterColumn("dbo.Addresses", "address2", c => c.String());
AlterColumn("dbo.Addresses", "address1", c => c.String());
}
}
If you delete the index and then excersize the code which is setup to run if there are any database migrations then all works well (no errors) as expected but if you delete the entire database (in a dev environment of course) and re-create the empty database then you will again see this error. Why is this error even happening in the first place?
