EF Migrations: Move Table from 2 Column PK to Single Column causes ALTER before DROP and fails

ef-code-first, entity-framework-migrations

Solution

You won't be able to do this with an automatic migration. You'll have to create a migration using `Add-Migration` and then change it so it only modifies the PK.

The migration can be as simple as:

public partial class TheMigration : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("Products", new[] { "Store", "Sku" });
        AddPrimaryKey("Products", "Sku");
    }

    public override void Down()
    {
        DropPrimaryKey("Products", new[] { "Sku" });
        AddPrimaryKey("Products", new[] { "Store", "Sku" });
    }
}

EF is altering the column because, when it's part of a `Key`, it's implicitly `NOT NULL`. You can leave it as-is, add a `[Required]` attribute, or allow EF to alter the column after dropping the PK.

Problem

I'm using EF 4.3.1 Code First Migrations. I have a table like: ``` public class Product { [Key] [Column(Order=0)] [MaxLength(100)] public string Store { get; set; } [Key] [Column(Order=1)] [MaxLength(100)] public string Sku { get; set; } }​ ``` I have an existing table created with the above code. I then moved it to a single-column Primary Key: ``` public class Product { [MaxLength(100)] public string Store { get; set; } [Key] [MaxLength(100)] public string Sku { get; set; } }​ ``` This causes EF to fail in the next automatic migration, complaining: ALTER TABLE [Product] ALTER COLUMN [Store] nvarchar The object 'PK_Product' is dependent on column 'Store'. ALTER TABLE ALTER COLUMN Store failed because one or more objects access this column. Clearly the PK_Product needs to be dropped before attempting to fire this ALTER statement (why is it altering the column at all?), but instead the migration fails. Am I doing something wrong or is this a bug? Workarounds?

Original source