Delete statement conflicts with Reference constraint

Question

I assume this has a very simple answer. I am brand new to entity framework and I am creating a test application consisting of Contacts and Groups as objects/entities.

Here is my code to delete a group:

    private void button_DeleteGroup_Click(object sender, EventArgs e)
    {
        var _selectedGroup = dataGridView_Groups.SelectedRows[0].DataBoundItem as Group;

        try
        {
            cgContext.Groups.Remove(_selectedGroup);
            cgContext.SaveChanges();

            PopulateGroupGrid();
            MessageBox.Show("Successfully deleted group from database!");
        }
        catch(Exception ex) { MessageBox.Show("Failed to delete group from database.\r\n\r\n" + ex); }
    }

If I delete a group that a contact belongs to, to test referential integrity, an exception is thrown (as it should):

"The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Contacts_dbo.Groups_Group_Id". The conflict occurred in database "ContactGroups", table "dbo.Contacts", column 'Group_Id'. The statement has been terminated."

I then catch this exception and display a message to the user. If I then go to add a new group or contact or do anything, the transaction fails with the same exception as before:

"The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Contacts_dbo.Groups_Group_Id". The conflict occurred in database "ContactGroups", table "dbo.Contacts", column 'Group_Id'. The statement has been terminated."

So, obviously i'm not clearing / ending a transaction or something when the initial exception occurs. What am I doing wrong or missing?

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

Rolling back from an invalid entity state in Entity Framework

In the case that a constraint violation, or other invalid entity state exists in Entity Framework, it is necessary to revert the state to a valid/good state in order to continue to persist changes.

This answer describes how to do that in detail.

Background - why FK constraint violation occurs

There exists a foreign key constraint between the Contacts table and the Groups table. Each contact has a reference to a group, per the foreign key (FK) FK_dbo.Contacts_dbo.Groups_Group_Id.

In the code above, a group is removed. Before doing this, either a check should be done to ensure that there are no Contacts that reference (are in) that group, or the Contacts in that group should be deleted. The approach will depend on your business logic.

Once the dependency has been removed (i.e. any Contacts that reference Group via Contacts.GroupId), then the Group can be removed without a FK violation.

edited May 23 '17 at 11:58

Community

1
1

answered May 29 '15 at 16:24

CJBS

15,147
6
86
135

Sorry, But this isn't about that error occurring. I am forcing it to occur so that I can test referential integrity. I want to know why the same exception continues to be thrown with other CRUD operation immediately after the exception is thrown the first time. – Michael May 29 '15 at 16:32
OK, so you're expecting the exception then. If you're saying that the problem is that you can't roll it back so that other changes can be tested, then have a look at this: http://stackoverflow.com/questions/5466677/undo-changes-in-entity-framework-entities – CJBS May 29 '15 at 16:34
Okay thanks, That sort of helped. This seems like it should have a more standardized answer. Thank again though :) – Michael May 29 '15 at 16:56
Sure. Sorry, I misread the question -- I should have picked up on the "as it should"! – CJBS May 29 '15 at 16:57

score 1 · Answer 2 · answered Jul 05 '17 at 06:35

use WillCascadeOnDelete(true) for WithOptional

  modelBuilder.Entity<Parent>()
  .HasMany<Child>(c => c.Children)
  .WithOptional(x => x.Parent)
  .WillCascadeOnDelete(true);

use Include

  var adv = db.Adv.Include(b => b.Features)
                  .Include(b => b.AdvDetails)
                  .Include(b => b.AdvGallery)
                  .FirstOrDefault(b => b.Id == id);
  db.Adv.Remove(adv);

for .HasMany(...).WithMany(...) Include is ok

score -1 · Answer 3 · answered May 29 '15 at 17:18

Entity Framework is also called Persistence Store. Because it persists status of an entity as well as original and modified values. It actually tracks entity.

EntityStateEntry has entry for each entity in EF. So when you call cgContext.Groups.Remove(_selectedGroup), it changes entity's state to deleted.

Now, you are deleting a group which has persons associated to it. So Referential integrity won't let you delete the _selectedGroup and you get the error. But the status of _selectedGroup was left as Deleted. Now when you go to add a new group, after adding group you call cgContext.SaveChange(). This tries to do both save changes - the new group added, and _selectedGroup deleted. But again, the deletion violates FK, and you get the same error.

To make it work, you can use TransactionScope. See usage here.

Thank you very much for your answer :) I will probably end up using that or creating a rollback method like this: public void Rollback() { dataContext.Dispose(); dataContext= new MyEntities(yourConnection); } Is the transactionScope a better way to go? — Michael, Jun 01 '15 at 16:22

Delete statement conflicts with Reference constraint

3 Answers3

Rolling back from an invalid entity state in Entity Framework

Background - why FK constraint violation occurs

Linked