adding a foreign key with Code First migration

ef-migrations entity-framework foreign-keys

Question

After adding a migration, I get an error while attempting to update my database.

These are the classes I had before adding 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 that I wanted to add a foreign key, I refactored the classes in the following manner:

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; }
}

To my migration, I includedAdd-Migration command. I included aUpdate-Database command and this is what I received:

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 fix this issue and restore the regularity of my migrations?

1
19
1/1/2018 12:13:59 PM

Popular Answer

Breaking your migration up into two migrations is the key to finding a solution to this issue. Add a nullable field first, then enter the information. Second, add a foreign key requirement to the field.

Initial Migration

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

    public class MyOtherEntity
    {
        public int Id { get; set; }
    }
    
    public class MyEntity
    {
        ...
        // New reference to MyOtherEntity
        public int? MyOtherEntityId { get; set; }
        ...
    }
    
  2. Establish a migration. NOTE: The name of the migration is not crucial, although using "AddBlogPosts1" makes it easier to read.

    > add-migration AddMyEntityMyOtherEntity1
    
  3. This ought to scaffold a migration that appears as follows:

    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. Add a default value for the new field by manually editing the migration that was generated. When the default value is invariant, it is the simplest case. If extra reasoning is required, it can be added to the SQL. In this example, we'll assume that all instances of MyEntity point to the same instance of MyOtherEntity 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. A database update

    > update-database
    

Following Migration

  1. Change the new property to stand in for a required foreign key in your MyEntity class by returning there.

    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. Make a new migration

    > add-migration AddMyEntityMyOtherEntity2
    
  3. This should produce a migration that looks like this:

    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. A database update

    > update-database
    

Other Remarks

  1. This method is effective for migrations used at application startup.
  2. It is possible to include more intricate mappings for the new column in the SQL, but those are not shown here.
44
3/28/2014 1:58:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow