Entity Framework The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Question

On updating database in Entity Framework , Code first Migration, I am getting this error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Clients_dbo.MedicalGroups_MedicalGroupId". The conflict occurred in database "hrbc", table "dbo.MedicalGroups", column 'Id'.

This is my class:

public partial class Client
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? MedicalGroupId { get; set; }
    [ForeignKey("MedicalGroupId")]
    public virtual MedicalGroups MedicalGroup { get { return _MedicalGroup; } set { _MedicalGroup = value; } }
}

Here is my 2nd class:

public partial class MedicalGroups
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

And this is my migration which I am trying to apply:

public override void Up()
{
    AddForeignKey("dbo.Clients", "MedicalGroupId", "dbo.MedicalGroups", "Id");
    CreateIndex("dbo.Clients", "MedicalGroupId");
}

Solution 1

I got the solution of my Problem. Problem is "data" which i have in my clients table. Because my client table have medicalgroupid values which are not actually exist that's why it is giving me error on foreign key constraint.

Update Client set MedicalGroupId = NULL

Solution 2

Check that there is not existing data in the database that is conflicting with the FK constraint causing the creation to fail.

Solution 3

I think @Cory was getting you close to the correct solution, you just did not take the time to investigate.

In add-migration code, the migration probably generated

public override void Up()
{
AddColumn("dbo.ClientContacts", "FamilialRelationshipId", c => c.Int(nullable: false));
CreateIndex("dbo.ClientContacts", "FamilialRelationshipId");
AddForeignKey("dbo.ClientContacts", "FamilialRelationshipId", "dbo.FamilialRelationships",        "FamilialRelationshipId");
}

Notice nullable:false; If your Model had an Id of int instead of int? (nullable int) the migration code will set nullable to false. Your model shows you are using a non-nullable int which defaults to 0, and you probably don't have a foreign key item with a value of 0.

Now, you will have to either create a default value, that exists in the foreign key table, or Create the Constraint with no check if you are using SQL Server to create the constraint. Remember this though: If you decorate your property with a [DefaultValue(0)] attribute, it won't change the existing data, like a SQL Column Add would, if a default value was specified.

I recommend you change your Model Class to allow a nullable int. Then, in your seed method, create a simple method against dbcontext to update the new column with a default value, because the [DefaultValue] attribute in data annotations will not modify your data.

Add-Migration / Update-Database to create the column and constraint. Next, modify your model if you desire to allow for a non-nullable int, and presuming you changed all rows to some valid value for the foreign key, Add-Migration / Update-database again. This gives you an unbroken chain in your model migration. Will come in handy later when you publish to a live site, because the flow of your data model changes will be intact.

• Hope this helps.

Solution 4

This error is telling you that you are violating the foreign key constraint. To resolve you have a few solutions

1. Fix your data - Somewhere there are records in the Clients table that have a MedicalGroupId that does not exist in the in the MedicalGroups table. Write a query to find out what IDs do not exist in the MedicalGroups table and manually fix the data yourself.
2. Remove the foreign key constraint - Obviously if you remove the foreign key constraint you will no longer be bothered by this message. Unfortunately the database will no longer enforce this relationship and might make this problem worse in the future.
3. Create constraint using WITH NOCHECK - You can create your foreign key constraint using the WITH NOCHECK option. This option tells SQL Server to not apply this constraint to existing data. SQL Server WILL check this constraint in any future INSERTS/UPDATES/DELETES.

Solution 5

this problem appear because your table is not empty. so you shoud add the new field without attaching it like foreign key. public int? MedicalGroupId { get; set; }.And execute update-database command in package manage console. Then fill the field in this table(client) with the right data (value exists in MedicalGroupsId). Insert the line to create the foreign key

[ForeignKey("MedicalGroupId")]
public virtual MedicalGroups MedicalGroup { get { return _MedicalGroup; } set { _MedicalGroup = value; } }

in the end execute the update-database command. It will be ok.

View more solutions

Comments

[AOZ]

but MedicalGroupId is not mandatory.. There should not be problem with the existing data

[Zaid Iqbal]

@turnkey yes there is already data in my client table but i cannot delete it.

[Zaid Iqbal]

@AOZ So do have any solution?

[AOZ]

i wrote my last comment to your post >> MedicalGroupId is existing field right? And then you want to relate to table after. Please check client table records which contains MedicalGroupId field is not null, then update all Clients to change MedicakGroupId field null

[Turnkey]

Check that any existing keys are pointing to valid MedicalGroups.Id.

[Ladislav Mrnka]

It is not about deleting data but about fixing them. If there is Client with reference to non existing MedicalGroup, you will never be able to add the FK.

[Turnkey]

In that case then please mark Cory or my answer as the solution as that's what we were advising to check.

[Salman Farsi]

I had the same issue, query the data as update script for the column, set column to null for all values. After that run migration, which successful and run the update script to put data back.

[Ari Roth]

We ran into a problem with the two-step solution here. If multiple migrations have to be applied at once, you end up with the following sequence: create the nullable field, make it non-nullable, then run the seed. There's no place to actually put data into the FK field, and everything explodes again. The only two solutions I've found around this are either to delete the data from the table that links to the FK, or make the field nullable and keep it that way.

[egmfrs]

@AriRoth +1 but surely there's another way, how common must this be?!

[Giannis Paraskevopoulos]

In my case i had set the foreign key property as int instead of int? so EF was adding a default value of 0 which was pointing to no entry in the foreign table.

[Gonzo345]

I thought it was something wrong related with my classes definitions. Oh my, thanks!