ALTER TABLE statement against FOREIGN KEY constraints

Question

When I run the following migration, I get the following error:

ALTER TABLE statement contradicts FOREIGN KEY constraint

I have an existing database and model refactoring to enable navigation properties.

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 contradicted the FOREIGN KEY constraint "FK_dbo.Students_dbo.Countries_CountryID". The conflict occurred in the database "aspnet-navprop-20141009041805", the table "dbo.Countries", in the column "ID".

Answer 1

I have the same problem, my table had data, so I changed the foreign key column to nullable.

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

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

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

Answer 2

I had the same problem and truncated the table with foreign key entries, and it succeeded.

Answer 3

If there is data in the table (Student Table), delete them, then try again.

Answer 4

Against your student object, you can mark the CountryId property as nullable using a question mark added to the type, i.e.

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