Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

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'.

like image 767
Barry MSIH Avatar asked Oct 11 '14 21:10

Barry MSIH


People also ask

How do you fix the alter table statement conflicted with the foreign key constraint?

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.

What is foreign key constraint error?

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.

Which of the following statements creates a foreign key in the already existing table reports?

WITH NOCHECK SQL Server will create the foreign key without verifying the existing data in the table.

How can check foreign key constraint in SQL Server?

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.


2 Answers

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");
}
like image 68
Mecit Semerci Avatar answered Sep 21 '22 18:09

Mecit Semerci


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

like image 38
Paul Oster Avatar answered Sep 24 '22 18:09

Paul Oster