CODEWITHSUNDEEP
entity-framework-5
Sachin Kainth
Sachin Kainth

Reputation: 46760

EF Migrations: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

I had these classes

public class Bid : ...
{
   ...

   [Required]
   public virtual TraderUser Trader { get; set; }
}

public class TraderUser : ...
{
   ...
}

I then changed these classes in the following way and added a new class

public class Bid : ...
{
   ...

   [Required]
   public virtual TraderUser TraderUser { get; set; }
}

public class TraderUser : ...
{
   ...

   public int TraderCompanyId { get; set; }

   [ForeignKey("TraderCompanyId")]
   public virtual TraderCompany TraderCompany { get; set; }
}

public class TraderCompany : ...
{
   ...
}

When I did an update-database I got the following error

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Bid_dbo.TraderUser_TraderUser_Id". The conflict occurred in database "LeasePlan.Development", table "dbo.TraderUser", column 'Id'.

I can't get the database to update. Any help is much appreciated.

Upvotes: 16

Views: 17067

Answers (2)

itaylorweb
itaylorweb

Reputation: 146

The alternative is to add an SQL statement within the migration code to insert a row before it adds the foreign keys. Here's an example of what I did:

        // Countries is a new table
        CreateTable(
            "dbo.Countries",
            c => new
                {
                    CountryID = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    Currency = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.CountryID);
        // Heres where i insert a row into countries
        Sql("INSERT INTO Countries (Name, Currency) VALUES ('United Kingdom', 0)");
        // I set the default value to 1 on the ID fields
        AddColumn("dbo.Brokers", "CountryID", c => c.Int(nullable: false, defaultValue: 1));
        AddColumn("dbo.Products", "CountryID", c => c.Int(nullable: false, defaultValue: 1));
        AddForeignKey("dbo.Brokers", "CountryID", "dbo.Countries", "CountryID", cascadeDelete: false);
        AddForeignKey("dbo.Products", "CountryID", "dbo.Countries", "CountryID", cascadeDelete: false);
        // Migrations then creates index's
        CreateIndex("dbo.Brokers", "CountryID");
        CreateIndex("dbo.Products", "CountryID");

Upvotes: 0

Minja
Minja

Reputation: 1222

Do not know if it is too late, but I had same problem and maybe this could help you.

I cannot see from your post, but probably your TraderUser table has some rows already inserted. What you are trying to accomplish is to create new table TraderCompany and create foreign key relationship in TraderUser that points to TraderCompany table.

In one migration you are trying to create non nullable foreign key relationship for table that already contains data.

You could try to the following:

  • First migration - everything same, except this line 

    public int TraderCompanyId { get; set; }

    should be 

    public int? TraderCompanyId { get; set; }

    This will allow you to create nullable foreign key column.

  • Update your TraderCompanyId column for existing data with some row from TraderCompany table.

  • Second migration - Change code from 

    public int? TraderCompanyId { get; set; }

    to

    public int TraderCompanyId { get; set; }

    and run your migration.

I hope this will help you.

Upvotes: 49

Related Questions