Cascade Delete Not Working with Breeze

Question

I have Child table that has foreign keys that reference two other tables, Parent1 & Parent2 (these are not the real names of these tables). Parent1 & Parent2 both have a one-to-many relationship with a Grandparent. The Child table is a join table establishing a many-to-many relationship between Parent1 and Parent2, but is exposed as its own "mapping entity" to Breeze.

I have cascade delete set on both of these foreign keys and when I delete a row in either the Parent1 or Parent2 tables from within SQL Server Management Studio, the cascade works properly and the corresponding rows in the Child table are deleted.

However when I execute the following code in my application using Breeze

function removeParent1(grandParent, parent1) {
    var index = grandParent.parent1s.indexOf(parent1);
    grandParent.parent1s.splice(index, 1);
    parent1.entityAspect.setDeleted();
}

I receive the following exception.

An exception of type 'System.Exception' occurred in Breeze.ContextProvider.dll but was not handled in user code

Additional information: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Child_dbo.Parent1_Parent1Id". The conflict occurred in database "DB", table "dbo.Parent1", column 'Id'.

The statement has been terminated.

I cannot figure out what's going on. Is Breeze attempting to update the Child row with a NULL foreign key?

Answer 1

Breeze does not YET support cascade deletes automatically. However, you can perform such deletes in code. Basically you have two choices when you are deleting a parent and its associated children and the order that you perform these operations has a significant effect on the process.

• Delete parent then delete children

  This will mark the parent as deleted and will force the update of the children's foreign keys to either null or the default value of the key depending on whether the foreign key property is nullable or not. The one exception to this rule is that breeze will never attempt to modify a primary key, so if the foreign key of the child is also part of the primary key then breeze will not attempt to modify it.

  All child navigation properties will now return either empty arrays or null depending on whether the navigation property is scalar or not. At this point each of the children will marked as modified, as a result of the foreign key change.

  Then the children will each be marked deleted.

• Delete children then parent ( recommended)

  The children will each be marked deleted. Foreign keys will NOT change but the corresponding navigation property will return null, instead of the returning the parent. At this point all of the parent's navigation properties that previously returned these children will now return either null or an empty array. The parent is marked deleted. No change is made to any of its children ( because it has none at this point).

So in your case I would try to delete the children first and then the parent. This way you are less likely to have the save fail on the server because none of the children are modified prior to being saved.