On Delete Cascade for Self-Referencing Table

On delete cascade for self-referencing table

Assuming you're keeping your FOREIGN KEY constraint in place, you cannot fix the issue in a FOR DELETE trigger. FOR triggers (also known as AFTER triggers) fire after the activity has taken place. And a foreign key will prevent a row from being deleted if it has references. Foreign key checks occur before deletion.

What you need is an INSTEAD OF trigger. You also need to bear in mind that your current trigger only tried to deal with one "level" of referencing. (So, if row 3 references row 2 and row 2 references row 1, and you delete row 1, your trigger only tried to remove row 2)

So, something like:

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    DELETE FROM Comments
    WHERE id in (select id from IDs);

If there are other (non-self-referencing) cascading foreign key constraints, they all have to be replaced by actions in this trigger. In such a case, I'd recommend introducing a table variable to hold the list of all IDs that will eventually be deleted from the Comments table:

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    declare @deletions table (ID varchar(7) not null);
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    insert into @deletions(ID)
    select ID from IDs

    -- Delete from FK referenced table
    DELETE FROM OtherTable
    WHERE CommentID in (select ID from @deletions)

    --This delete comes last
    DELETE FROM Comments
    WHERE id in (select ID from @deletions);

MySQL cascade delete on self-referencing parent-child table?

When you enter any value into the parent_id column of a new row, the foreign key requires that there is a row where id has that value, because that's how foreign keys work.

If you want to use a foreign key with your self-referencing relationship, you should use NULL instead of 0 for the parent_id of a row that has no parent to reference. Foreign keys ignore NULL.

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

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

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);
    }
}

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 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.

---

Related Topics