CODEWITHSUNDEEP
sql-serverentity-frameworkentity-framework-migrations
Barry MSIH
Barry MSIH

Reputation: 3787

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

Upvotes: 11

Views: 18407

Answers (4)

Hakan Fıstık
Hakan Fıstık

Reputation: 19421

If there is a data in the table (Student table) delete them and then re-try again.

Upvotes: 3

Ciaran Gallagher
Ciaran Gallagher

Reputation: 4020

Against your Student entity you can mark your CountryId property as nullable using a question mark appended 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; }
}

Upvotes: 2

Mecit Semerci
Mecit Semerci

Reputation: 191

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

Upvotes: 8

Paul Oster
Paul Oster

Reputation: 51

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

Upvotes: 5

Related Questions