即使我已投票关闭了重复，我想我要发布一个答案，因为再三考虑，重复问题的答案在问题编辑后最终变得有些混乱，并且最近添加的语句

Even though I have voted to close as duplicate, I think I'm going to post an answer, because, on a second thought, the duplicate question's answer ended up being a little bit confusing after the question edits, and the lately added statement that

我想您只需要从类别的递归外键中删除该ON DELETE CASCADE标志即可。 CAT_SCH的外键上的CASCADE标志应该没关系

I guess you just need to drop that ON DELETE CASCADE flag from your recursive foreign key in Categories. The CASCADE flag on the foreign key from CAT_SCH should not matter

实际上不是真的（SQL Server会在删除时引发错误，因为在一个字段上的级联将与在另一个字段上的不采取行动相冲突）。

is actually not true (SQL Server will raise an error on a delete, because cascade on one field will conflict with no action on another field).

主要要点仍然存在：

• 在不执行任何删除操作时，您将两个外键都声明为 。


• 您在 main表上创建一个而不是delete 触发器，该触发器将删除必需的子级，然后删除主记录本身。

• You declare both foreign keys as on delete no action.
• You create an instead of delete trigger on the "main" table that will delete required children, in order, and then delete the "main" record itself.

例如（ SQL小提琴）：

create table main(id int not null primary key);

create table nodes (
  nodeID int not null primary key,
  fkID int null foreign key references main(id),
  parentID int null foreign key references nodes(nodeID)
);

create trigger dlt on main
instead of delete
as
begin

  declare @to_delete table (nodeID int not null, level int not null, primary key(level, nodeID));

  begin tran;

  with cte as (
    select n.nodeID, 0 as level
    from nodes n inner join deleted d on n.fkID = d.id

    union all

    select n.nodeID, level + 1
    from nodes n inner join cte c on n.parentID = c.nodeID
  )
  insert into @to_delete(nodeID, level)
  select nodeID, level
  from cte;

  declare cur cursor
  local
  forward_only
  read_only
  for
    select distinct level from @to_delete order by level desc;

  open cur;

  declare @cur_level int;

  fetch next from cur into @cur_level;
  while @@fetch_status = 0
  begin
    delete from nodes
    from nodes n inner join @to_delete d on n.nodeID = d.nodeID
    where d.level = @cur_level;

    fetch next from cur into @cur_level;
  end;


  close cur;
  deallocate cur;

  delete from main from main m inner join deleted d on m.id = d.id;

  commit tran;
end;