none
A referential integrity constraint violation occurred

    Question

  • I previously created my question at stackoverflow however, i can't seem to get an answer.

    http://stackoverflow.com/questions/19074978/foreign-key-conflict

    I hope that i could get an answer here. thanks in advance

    When i Update Customer.City = 2, and then update Site.City = 2; it will save. however if they're are not the same it will throw "A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship.". i don't know why it is happening

    here's the DDL http://pastebin.com/ZYwKib83

    //SiteModel
    private static IQueryable<Site> Build(this DbSet<Site> query)
    {
        return query.Include("User").Include("City").Include("Country").Include("Customer");
    }
    
    public static Site Find(int siteID)
    {
        using (DragonRentalsEntities context = new DragonRentalsEntities(new ConfigurationManager().ConnectionString))
        {
            Site result = context.Sites.Build().SingleOrDefault(s => s.ID == siteID);
            return result;
        }
    }
    
    public static Site Update(Site _updatedSite)
    {
        using (DragonRentalsEntities context = new DragonRentalsEntities(new ConfigurationManager().ConnectionString))
        {
            context.Sites.Attach(_updatedSite);
            context.Entry(_updatedSite).State = EntityState.Modified;
            context.SaveChanges();
            return Find(_updatedSite.ID);
        }
    }
    

    here's what i tried.

    	Site newSite = new Site();
    	newSite.Name = "New Site 1";
    	newSite.Address = "New Site Address";
    	newSite.CityID = 1;
    	newSite.CountryID = 1;
    	newSite.CustomerID = 1;
    	newSite.EncodedBy = 1;
    	newSite.DateEncoded = DateTime.Now;
    
    	SiteModel.Insert(newSite); //insert new record
    
    	Site test = SiteModel.Find(12); //find the newly inserted id
    	test.City = null;
    	test.CityID = 2; //update the CityID to 2
    
    	SiteModel.Update(test); //will throw the error
    To Ensure we have the same data. Customer with ID 1 should have CityID and CountryID both having a value of 1





    • Edited by MatthewChen_ Saturday, October 12, 2013 10:54 PM
    Saturday, October 12, 2013 10:53 PM

Answers

  • Hi MatthewChen_,

    For detached object, if we do not like to do the update operation in one context, we need to create a new instance for the entity like below:

     public static Site Update(Site _updatedSite)
            {
                Site site = new Site()
                {
                    ID = _updatedSite.ID,
                    Name = _updatedSite.Name,
                    Address = _updatedSite.Address,
                    CityID = _updatedSite.CityID,
                    CountryID = _updatedSite.CountryID,
                    CustomerID = _updatedSite.CustomerID,
                    DateEncoded = _updatedSite.DateEncoded,
                    EncodedBy = _updatedSite.EncodedBy
                };
    
                using (TestDB1Entities context = new TestDB1Entities())
                {
                    context.Sites.Attach(site);
                    context.Entry(site).State = EntityState.Modified;
                    context.SaveChanges();
                    return Find(_updatedSite.ID);
                }
            }

    In the main method, we call it the same as what you have done.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by MatthewChen_ Wednesday, October 16, 2013 7:53 AM
    Tuesday, October 15, 2013 6:33 AM
    Moderator

All replies

  • Hello,

    Welcome to this forum.

    For the error message “A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship”, as far as I know, it may cause by two features:

    1. The CityID does not equal the id of City entity
    2. The City entity has been set to be null.

    If we want to attach an entity that has relationship with other entities to a new context instance, we should avoid features above. In other words, Entity Framework does not support operation like this.

    So for achieving what we want, we need to write the operation in one context instance as below:

    using (TestDB1Entities context = new TestDB1Entities())
    
                    {
    
                        Site test = context.Sites.Find(6); //find the newly inserted id
    
                        test.CityID = 2; //update the CityID to 2
    
                        context.SaveChanges();
    
                    }
    

    Thanks &Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 14, 2013 8:45 AM
    Moderator
  • Hello Fred,

    Thanks for your reply, i'm working with detached object though. what is the best practice for updating detached objects?

    Monday, October 14, 2013 12:30 PM
  • Hi MatthewChen_,

    For detached object, if we do not like to do the update operation in one context, we need to create a new instance for the entity like below:

     public static Site Update(Site _updatedSite)
            {
                Site site = new Site()
                {
                    ID = _updatedSite.ID,
                    Name = _updatedSite.Name,
                    Address = _updatedSite.Address,
                    CityID = _updatedSite.CityID,
                    CountryID = _updatedSite.CountryID,
                    CustomerID = _updatedSite.CustomerID,
                    DateEncoded = _updatedSite.DateEncoded,
                    EncodedBy = _updatedSite.EncodedBy
                };
    
                using (TestDB1Entities context = new TestDB1Entities())
                {
                    context.Sites.Attach(site);
                    context.Entry(site).State = EntityState.Modified;
                    context.SaveChanges();
                    return Find(_updatedSite.ID);
                }
            }

    In the main method, we call it the same as what you have done.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by MatthewChen_ Wednesday, October 16, 2013 7:53 AM
    Tuesday, October 15, 2013 6:33 AM
    Moderator
  • Thanx Fred for your answer.

    i was also doing that before, before i tried something else. "if it ain't broke don't fix it." thanx again :)

    Wednesday, October 16, 2013 7:53 AM