adding a foreign key with Code First migration

2019-02-02 01:16发布

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?

5条回答
Rolldiameter
2楼-- · 2019-02-02 01:30

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.
查看更多
Rolldiameter
3楼-- · 2019-02-02 01:31

I came across this problem today. Here's how I handled it. I did have to make my FK property nullable which was not a big deal in my case.

I made my changes to my POCO, generated the migration and then added the following to the migration.

public partial class LineItemProductionHistory_v4 : DbMigration
{
    public override void Up()
    {
        AddColumn("LineItemProductionHistories","TempLineItemId",c=>c.Int());
        Sql("Update LineItemProductionHistories Set TempLineItemId = LineItem_Id");
       .
       . Generated code
       .
        Sql("Update LineItemProductionHistories Set LineItemId = TempLineItemId");
        DropColumn("LineItemProductionHistories", "TempLineItemId");
    }
}

I'm just temporarily storing all off the foreign keys, letting the migration do it's add/drop stuff and then putting the foreign keys back in the newly created FK.

查看更多
放我归山
4楼-- · 2019-02-02 01:40

The quick answer is - firstly add a nullable column for ProductId, then set some default value in all existing rows, then set the column to non null (if you need that) for future inserts, add finally add the foreign key constraint.

I wrote a long blog post on just this with full source code included - http://nodogmablog.bryanhogan.net/2015/04/entity-framework-non-null-foreign-key-migration/

查看更多
Juvenile、少年°
5楼-- · 2019-02-02 01:47

It can occur when you try to add foreign key constraint on a non nullable column of a table that already contains data. If your tables contain data try to delete them first and retry to update your database.

查看更多
成全新的幸福
6楼-- · 2019-02-02 01:51

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");
    }
查看更多
登录 后发表回答