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'.
You can use With NoCheck with alter table statement and it will ignore the check to validate data and create Foreign Key Constraint. Once the Foreign Key Constraint will be created, it will enforce integrity for any new records inserted.
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
WITH NOCHECK SQL Server will create the foreign key without verifying the existing data in the table.
Using SQL Server Management Studio Open the Table Designer for the table containing the foreign key you want to view, right-click in the Table Designer, and choose Relationships from the shortcut menu. In the Foreign Key Relationships dialog box, select the relationship with properties you want to view.
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");
}
I had this same issue and truncated the table with the foreign key records and it succeeded.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With