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

Question

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.

Answer 1

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.