The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

ef-migrations entity-framework sql-server

Question

When I run the following migration, I am getting the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

I have an existing database and refactoring the model to include a navigation property.

See the original model and then the new model:

Original model:

public class Student
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

New model:

public class Student
{
     public int ID { get; set; }
     public string Name { get; set; }
     public int CountryID { get; set; }
     public virtual Country Country { get; set; }
}

public class Country 
{
     public int ID { get; set; }            
     public string Country { get; set; }
}

Add-Migration navigation property:

public override void Up()
{
            CreateTable(
                "dbo.Countries",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        CountryName = c.String(),
                    })
                .PrimaryKey(t => t.ID);

            AddColumn("dbo.Students", "CountryID", c => c.Int(nullable: false));
            CreateIndex("dbo.Students", "CountryID");
            AddForeignKey("dbo.Students", "CountryID", "dbo.Countries", "ID", cascadeDelete: true);
            DropColumn("dbo.Students", "Country");
}

Update-Database error:

System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Students_dbo.Countries_CountryID". The conflict occurred in database "aspnet-navprop-20141009041805", table "dbo.Countries", column 'ID'.

1
8
10/11/2014 9:19:06 PM

Accepted Answer

I got same problem, my table had data therefore I changed foreign key column to nullable.

AddColumn("dbo.Students", "CountryID", c => c.Int(nullable: true));

You should change your code like that then run again Update-Database -Verbose

public override void Up()
{
            CreateTable(
                "dbo.Countries",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        CountryName = c.String(),
                    })
                .PrimaryKey(t => t.ID);

            AddColumn("dbo.Students", "CountryID", c => c.Int(nullable: true));
            CreateIndex("dbo.Students", "CountryID");
            AddForeignKey("dbo.Students", "CountryID", "dbo.Countries", "ID", cascadeDelete: true);
            DropColumn("dbo.Students", "Country");
}
6
2/28/2017 8:33:23 AM

Popular Answer

I had this same issue and truncated the table with the foreign key records and it succeeded.



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