adding a foreign key with Code First migration

Question

I have an error when trying to update my database after adding a migration.

Here are my classes before add-migration

public class Product
{
    public Product() { }

    public int ProductId { get; set; } 
    public string Name { get; set; }
    public decimal Price { get; set; }
    public bool Istaxable { get; set; }
    public string DefaultImage { get; set; }
    public IList<Feature> Features { get; set; }
    public IList<Descriptor> Descriptors { get; set; }
    public IList<Image> Images { get; set; }
    public IList<Category> Categories { get; set; }
}


public class Feature
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }

}

Now I wanted to add a foreign key in my Feature class and refactored the classes this way:

public class Product
{
    public Product() { }

    public int ProductId { get; set; } 
    public string Name { get; set; }
    public decimal Price { get; set; }
    public bool Istaxable { get; set; }
    public string DefaultImage { get; set; }
    public IList<Feature> Features { get; set; }
    public IList<Descriptor> Descriptors { get; set; }
    public IList<Image> Images { get; set; }
    public IList<Category> Categories { get; set; }
}

public class Feature
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string Image { get; set; }
    public string VideoLink { get; set; }

    public int ProductId { get; set; }
    public Product Product { get; set; }
}

I added a migration with Add-Migration command. I added an Update-Database command and here is what I got back:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.ProductFeatures_dbo.Products_ProductId". The conflict occurred in database "CBL", table "dbo.Products", column 'ProductId'

What can I do to solve this problem and get my migrations back to normal?

Answer 1

The key to solving this problem is to break your migration into two migrations. First, add a nullable field and fill in the data. Second, make the field a required foreign key.

First Migration

1. Add the new property to your class as a nullable type (e.g. int?)

   public class MyOtherEntity
   {
       public int Id { get; set; }
   }
   
   public class MyEntity
   {
       ...
       // New reference to MyOtherEntity
       public int? MyOtherEntityId { get; set; }
       ...
   }
   

2. Create a migration. NOTE: Migration name is not important, but something like "AddBlogPosts1" makes it easy to read.

   > add-migration AddMyEntityMyOtherEntity1
   

3. This should scaffold a migration that looks like this:

   public partial class AddMyTableNewProperty1 : DbMigration
   {
       public override void Up()
       {
           AddColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int());
       }
       public override void Down()
       {
           DropColumn("dbo.MyEntity", "MyOtherEntityId");
       }
   }
   

4. Now manually edit the generated migration to add a default value for the new field. The easiest case is when the default value is invariant. You can add more logic in the SQL if needed. This example assumed all the MyEntity instances point to the same MyOtherEntity instance with ID 1.

   public partial class AddMyTableNewProperty1 : DbMigration
   {
       public override void Up()
       {
           AddColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int());
   
           // ADD THIS BY HAND
           Sql(@"UPDATE dbo.MyEntity SET MyOtherEntityId = 1
                 where MyOtherEntity IS NULL");
       }
       public override void Down()
       {
           DropColumn("dbo.MyEntity", "MyOtherEntityId");
       }
   }
   

5. Update the database

   > update-database
   

Second Migration

1. Go back to your MyEntity class and change the new property to represent a mandatory foreign key.

   public class MyEntity
   {
       ...
       // Change the int? to int to make it mandatory
       public int MyOtherEntityId { get; set; }
   
       // Create a reference to the other entity
       public virtual MyOtherEntity MyOtherEntity { get; set; }
       ...
   }
   

2. Create another migration

   > add-migration AddMyEntityMyOtherEntity2
   

3. This should create a migration like the following:

   public partial class AddMyEntityMyOtherEntity2: DbMigration
   {
       public override void Up()
       {
           AlterColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int(nullable: false));
           CreateIndex("dbo.MyEntity", "MyOtherEntityId");
           AddForeignKey("dbo.MyEntity", "MyOtherEntityId", "dbo.MyOtherEntity", "Id");
       }
       public override void Down()
       {
           DropForeignKey("dbo.MyEntity", "MyOtherEntityId", "dbo.MyOtherEntity");
           DropIndex("dbo.MyEntity", new[] { "MyOtherEntityId" });
           AlterColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int());
       }
   }
   

4. Update the database

   > update-database
   

Other Notes

1. This technique works for migrations applied during application start up.
2. Adding more complex mappings for the new column in the SQL are possible, but not illustrated here.

Answer 2

This is an old issue but currently there is no need to create a separate migration and this issue can be solved using a few steps:

1. Run Add-Migration with the entity changes (a new non-nullable reference property added, ProductId in this case) to scaffold a new migration class
2. Modify newly added migration to create a nullable column (nullable: true) instead of non-nullable
3. Below AddColumn add Sql command setting the value of the column as needed
4. Below add AlterColumn command which will make the column non-nullable as intended

In the example above this would look like:

    public override void Up()
    {
        AddColumn("dbo.ProductFeatures", "ProductId", c => c.Int(nullable: true));
        Sql("UPDATE [dbo].[ProductFeatures] SET ProductId = (SELECT TOP 1 [Id] FROM [dbo].[Products])");
        AlterColumn("dbo.ProductFeatures", "ProductId", c => c.Int(nullable: false));
        CreateIndex("dbo.ProductFeatures", "ProductId");
        AddForeignKey("dbo.ProductFeatures", "ProductId", "dbo.Products", "Id");
    }