I have an error when trying to update my database after adding a migration.
Here are my classes before add-migration
public class Product
{
public Product() { }
public int ProductId { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public bool Istaxable { get; set; }
public string DefaultImage { get; set; }
public IList<Feature> Features { get; set; }
public IList<Descriptor> Descriptors { get; set; }
public IList<Image> Images { get; set; }
public IList<Category> Categories { get; set; }
}
public class Feature
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
}
Now I wanted to add a foreign key in my Feature class and refactored the classes this way:
public class Product
{
public Product() { }
public int ProductId { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public bool Istaxable { get; set; }
public string DefaultImage { get; set; }
public IList<Feature> Features { get; set; }
public IList<Descriptor> Descriptors { get; set; }
public IList<Image> Images { get; set; }
public IList<Category> Categories { get; set; }
}
public class Feature
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public string Image { get; set; }
public string VideoLink { get; set; }
public int ProductId { get; set; }
public Product Product { get; set; }
}
I added a migration with Add-Migration
command.
I added an Update-Database
command and here is what I got back:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.ProductFeatures_dbo.Products_ProductId". The conflict occurred in database "CBL", table "dbo.Products", column 'ProductId'
What can I do to solve this problem and get my migrations back to normal?
The key to solving this problem is to break your migration into two migrations. First, add a nullable field and fill in the data. Second, make the field a required foreign key.
First Migration
Add the new property to your class as a nullable type (e.g. int?)
Create a migration. NOTE: Migration name is not important, but something like "AddBlogPosts1" makes it easy to read.
This should scaffold a migration that looks like this:
Now manually edit the generated migration to add a default value for the new field. The easiest case is when the default value is invariant. You can add more logic in the SQL if needed. This example assumed all the MyEntity instances point to the same MyOtherEntity instance with ID 1.
Update the database
Second Migration
Go back to your MyEntity class and change the new property to represent a mandatory foreign key.
Create another migration
This should create a migration like the following:
Update the database
Other Notes
I came across this problem today. Here's how I handled it. I did have to make my FK property nullable which was not a big deal in my case.
I made my changes to my POCO, generated the migration and then added the following to the migration.
I'm just temporarily storing all off the foreign keys, letting the migration do it's add/drop stuff and then putting the foreign keys back in the newly created FK.
The quick answer is - firstly add a nullable column for ProductId, then set some default value in all existing rows, then set the column to non null (if you need that) for future inserts, add finally add the foreign key constraint.
I wrote a long blog post on just this with full source code included - http://nodogmablog.bryanhogan.net/2015/04/entity-framework-non-null-foreign-key-migration/
It can occur when you try to add foreign key constraint on a non nullable column of a table that already contains data. If your tables contain data try to delete them first and retry to update your database.
This is an old issue but currently there is no need to create a separate migration and this issue can be solved using a few steps:
In the example above this would look like: