Fix 'The DELETE statement conflicted with the REFERENCE constraint' error with code

Question

I have an Asp.Net MVC 5 website with EntityFramework Code First. In my site, I have a Restaurant model with the following code:

public class Restaurant
{
    [Required]
    public string Name { get; set; }

    //....
    public virtual IList<RestaurantType> Types { get; set; }
}

And the code for the RestaurantType is:

public class RestaurantType
{
    [Key]
    public int ID { get; set; }

    [Required]
    public string Type { get; set; }

    public virtual Restaurant Restaurant { get; set; }
}

When I try to delete the restaurant from context, I get the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.RestaurantTypes_dbo.Restaurants_Restaurant_ID". The conflict occurred in database "aspnet-Test-20131111052251", table "dbo.RestaurantTypes", column 'Restaurant_ID'. The statement has been terminated.

I have data in production and I want to handle this as silently as possible. I tried the following code:

            for (int i = 0; i < restaurant.Types.Count; i++)
            {
                var type = restaurant.Types[i];
                db.RestaurantTypes.Remove(type);
                restaurant.Types.Remove(type);
            }

            db.Restaurants.Remove(restaurant);
            await db.SaveChangesAsync();

But I get the same error. I checked the database and there's no row with the restaurant ID. I even tried this:

        var list = db.RestaurantTypes.Where(t => t.Restaurant == null || t.Restaurant.ID == restaurant.ID);
        foreach (var ib in list)
        {
            db.RestaurantTypes.Remove(ib);
        }
        db.SaveChanges();

It didn't work either. Is there a way for me to fix this with C# code?

Why has a restaurant a list of restaurant-types? Can it be of multiple types? Also, why does a restaurant-type has a reference to a restaurant? — Tim Schmelter, Feb 03 '14 at 08:52
@TimSchmelter the restaurant has a list of types, yes. Think of it as some kind of category. For instance a restaurant can be both Italian and Indian! Our system needs it. At first the reference to `restaurant` wasn't available. I just added that to write the last snippet. (check if the `Restaurant` field is `NULL`) I get the error no matter if it's there or not. — Alireza Noori, Feb 03 '14 at 09:35

score 7 · Answer 1 · edited May 23 '17 at 12:17

Depending on your model, Remove might not actually delete the row in RestaurantTypes. Try using DeleteObject instead (see Entity Framework .Remove() vs. .DeleteObject()). Also to be sure that the deletion of Restaurant is not happening before the deletion of all RestaurantTypes, try commiting the changes after deletion of all RestaurantTypes:

for (int i = 0; i < restaurant.Types.Count; i++)
{
    var type = restaurant.Types[i];
    db.DeleteObject(type);
    restaurant.Types.Remove(type);
}
db.SaveChanges();

db.DeleteObject(restaurant);
db.SaveChanges();

Also, on a side note (this has nothing to do with your error message): Aren't the relationships in your database model backwards. If a Restaurant can have one or many RestaurantTypes, you should have a references from the Restaurant to the RestaurantTypes. That means you need an additional table that holds the relationships (e.g. RelRestaurantRestaurantType):

---------------                      ------------------                          ------------------ 
|             |                      | Rel            |                          |                |
| Restaurant  | <-- Restaurant_ID -- | Restaurant     | -- RestaurantType_ID --> | RestaurantType |
|             |                      | RestaurantType |                          |                |
---------------                      ------------------                          ------------------

The way you are doing it, each Restaurant would have it's own copies of types. E.g. let's say you have a type "Chinese" and you are having 100 chinese restaurants. Then you would end up with 100 "Chinese" entries in your table RestaurantType.

score 0 · Answer 2 · answered Feb 03 '14 at 09:17

dispute the logic you have ( Restaurant and Restaurant Type), the Restaurant should be child of Restaurant Type.

what you are trying to do is a cascade delete to get this, you should have keys in both tables

public class Restaurant{
    public Restaurant()
    {
        Types=new List<RestaurantType>();
    }
    [Key]
    public int Id{get; set;}
    [Required]
    public string Name{get; set;}

    public virtual ICollection<RestaurantType> Types{get;set;}
}

public class RestaurantType{
   [Key]
   public int Id{get;set;}
   [Required]
   public string Type{get; set;}
   public int RestaurantId{get; set;}

   [ForeignKey("RestaurantId")]
   public Restaurant Restaurant{get;set;}    
}

and your code will execute correctly but be sure that your logic is correct, actually as what i know the restaurant has only one type and not many

regards

Actually, I don't think much of a difference in your code. The only change is that you've added the `ForeignKey` attribute which EF infers from class definition. Just to be sure, I can test it. Are you sure it's the reason I get this error? — Alireza Noori, Feb 03 '14 at 09:32
i forgot to mention, sometimes you need to make the entity state to deleted,by this you are ensuring that the EF will delete the rows by using : db.Entity(type).State=EntityState.Deleted; ... hope it will help you — Monah, Feb 03 '14 at 09:55

Fix 'The DELETE statement conflicted with the REFERENCE constraint' error with code

2 Answers2