Adding foreign key with new table with automatic object structure mvc migrations C #
I would like to add a new table and foreign key to an existing table in SQL Server with the first code auto-wrap. The only problem I have is that there is data in the table and I cannot set the foreign key to the default for the existing data.
Example:
Car table
Name Color
Matrix Blue
Camry Red
Now I want to add the Brand table:
Car table
Id Name Color BrandId
1 Matrix Blue 1
2 Camry Red 1
Brand table
Id Name
1 Toyota
The question is, how can I set the default BrandId to 1 in my car model without the foreign key constraint error when running the Update-Database command?
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Cars_dbo.Brands_BrandId". The conflict occurred in database "Example", table "dbo.Brands", column 'Id'.
Now, in my Configuration.cs file, I add my car brands like this:
context.Brands.AddOrUpdate(
new Brand{ Id = 1, Name = "Toyota" }
);
In my context, I override the void OnModelCreating guarded override (DbModelBuilder modelBuilder) and apply this relationship:
modelBuilder.Entity<Car>().HasRequired<Brand>(t => t.Brand);
In my car model:
[Required( ErrorMessage = "The brand is mandatory" )]
public int BrandId { get; set; }
[ForeignKey( "BrandId" )]
public virtual Brand Brand { get; set; }
In the constructor of the Car model, I set a default value for the BrandId:
public Car()
{
BrandId = 1;
}
source to share
The "automatic migration" in EF is a bit ambiguous, but if you mean one that tries to change your db for you, you can't. You have to use the migration form ** where you use Add-Migration
from the visual studio console to create the migration classes. This is usually a much better way to handle migrations.
For this I would normally use the function Sql
to turn off constraint checking.
Sql("ALTER TABLE Cars NOCHECK CONSTRAINT ALL")
add FK, do some logic to populate that column and then re-enable constraint checking.
Sql("ALTER TABLE Cars CHECK CONSTRAINT ALL")
Another possibility is maybe the cars are already in the system in which you want to have brands null
, but any future cars will need to reference an existing brand. In this case, you enable null
in that column and handle the validation request in your code.
In fact, you should always handle all validation in code, never rely on your database to do the validation for you.
** These are still sometimes referred to as automatic transitions because you usually install them automatically when your application starts.
source to share
I had this problem too and found a way to solve this problem.
at first you only have to define public int BrandId { get; set; }
and run the project with this. then in your database put a valid value (primary key for your foreign key) to BrandId
. Now you can define public virtual Brand Brand { get; set; }
, run the project again and you won't get more errors.
This may not be the best solution to this problem, but if you have a primary key for your foreign key, you can use this method.
source to share