Cascade Delete in Many-To-Many Self-Reference Table

CASCADE Delete in many-to-many self-reference table

Read this KB article, which says the following among other things...

You receive this error message because in SQL Server, a table cannot
appear more than one time in a list of all the cascading referential
actions that are started by either a DELETE or an UPDATE statement.
For example, the tree of cascading referential actions must only have
one path to a particular table on the cascading referential actions
tree.

To do what you want, the DISPLAY_TAB_GROUPING table would have to appear twice. I suggest you use a stored proc that implements your delete code instead.

How to delete on cascade a self reference relationship?

You are trying to do another DELETE in a DELETE trigger which is disallowed as this can go into an infinite loop.

You might want to change your use a INSTEAD OF DELETE trigger (see this link) and change your trigger body code to something like below

UPDATED: To address the error that @TT pointed out.

CREATE TABLE #CascadeDeleteRows (IDTableA int, IDTableARoot int)

INSERT 
  INTO #CascadeDeleteRows 
SELECT b.IDTable
     , b.IDTableARoot 
  FROM TableA
 WHERE b.IDTableA IN (SELECT deleted.IDTableARoot from deleted)

DELETE 
  FROM TableA 
 WHERE IDTableA IN (SELECT #CascadeDeleteRows.IDTableA FROM #CascadeDeleteRows)

DROP TABLE #CascadeDeleteRows

Hope this helps

Implementing Cascade Delete in a self referencing table in EF Core 2

The problem solved by recursive method:

[HttpPost]
public async Task<JsonResult> DeleteComment([FromBody] DeleteCommentViewModel obj)
{
    if (ModelState.IsValid)
    {
       var comment = await 
      _commentRepository.GetAll().SingleOrDefaultAsync(m => m.Id == obj.CommentId);
      if (comment != null)
      {
          await RemoveChildren(comment.Id);
         _commentRepository.Delete(comment);
      }
      if (Request.IsAjaxRequest())
      {
          return Json(1);
      }
   }
   return Json(new { code = 0 });
}

async Task RemoveChildren(int i)
{
    var children = await _commentRepository.GetAll().Where(c => c.ParentId = i).ToListAsync();
        foreach (var child in children)
    {
       await RemoveChildren(child.Id);
       _commentRepository.Delete(child);
    }
}

On DELETE CASCADE fails in self referencing MySQL table having depth more than 15 levels

This is documented behavior:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same
table it has previously updated during the cascade, it acts like
RESTRICT. This means that you cannot use self-referential ON UPDATE
CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite
loops resulting from cascaded updates. A self-referential ON DELETE
SET NULL, on the other hand, is possible, as is a self-referential ON
DELETE CASCADE. Cascading operations may not be nested more than 15
levels deep.

Source: InnoDB and FOREIGN KEY Constraints, Referential Actions

How to set on delete cascade for self reference Foreign Key in Entity Framework in ASP.NET

i also faced the similar issue, and if i remember correctly what i found is EF doesn't support cassade delete on self reference, so we need to handle it by code. What i followed is

• Remove the cascade delete from fluent api or generated migration.
  
• Add code to delte/setnull all self-reference and then delete.

Cascade delete from self-referential many-to-many relationship

I figured it out. As stated in an edit to the OP, I simply had to create a new relation with the criteria swapped. This way both relationships are loaded and deleted when the fit is deleted:

mapper(Fit, fits_table,
   properties = {
                 "_Fit__projectedFits" : relation(Fit,
                                                  primaryjoin = projectedFits_table.c.victimID == fits_table.c.ID,
                                                  secondaryjoin = fits_table.c.ID == projectedFits_table.c.sourceID,
                                                  secondary = projectedFits_table,
                                                  collection_class = HandledProjectedFitList),

                 "_Fit__projectedOnto" : relation(Fit,
                                                  primaryjoin = fits_table.c.ID == projectedFits_table.c.sourceID,
                                                  secondaryjoin = fits_table.c.ID == projectedFits_table.c.victimID == fits_table.c.ID,
                                                  secondary = projectedFits_table,
                                                  collection_class = HandledProjectedFitList)

---

Related Topics