删除具有子项和自引用约束的记录

Question

所以我有以下表格（省略了不相关的列）：

CREATE TABLE [dbo].[Step]
(
   [StepId] INT NOT NULL PRIMARY KEY IDENTITY, 
   [ParentStepId] INT NULL,
   CONSTRAINT [FK_Step_ParentStep] FOREIGN KEY ([ParentStepId]) REFERENCES [Step]([StepId])
)
CREATE TABLE [dbo].[StepInput]
(
   [StepInputId] INT NOT NULL PRIMARY KEY IDENTITY, 
   [StepId] INT NOT NULL, 
   [ChildStepId] INT NULL,
   CONSTRAINT [FK_StepInput_Step] FOREIGN KEY ([StepId]) REFERENCES [Step]([StepId]), 
   CONSTRAINT [FK_StepInput_ChildStep] FOREIGN KEY ([ChildStepId]) REFERENCES [Step]([StepId]),
)

有一个Step，它有零到多个StepInput。StepInput有一个可选的子步骤，步骤有一个可选择的父步骤（自引用）。

这是意料之中的事。我现在想做的是删除一个步骤，并删除与该步骤相关的所有StepInputs，以及任何子步骤及其输入。

我使用的是实体框架5。有没有一种方便的方法可以用EF实现这一点，或者我需要创建一个存储过程，在FK约束上设置级联选项，或者还有其他更好的解决方案吗？

我确实尝试过以各种方式使用ONDELETE级联，但我尝试过的都不起作用。我还读到，在分层数据方面，你不应该依赖级联删除，但我并不真正理解建议的替代方案（CTE？）

如果我取消ParentStepId列，事情会变得更简单吗？现在唯一真正有用的是确定该步骤是否是顶级步骤，我可能也可以很容易地使用一个位字段。不存在不包含StepInput的父子关系。

很明显，当涉及到SQL，尤其是SQL Server时，我是非常环保的，所以在回答时请记住这一点；）

Answer 1

我已经尝试并创建了一个简单的过程来删除给定stepId 的所有行

DECLARE @stepId int
SET @stepId = 1
DECLARE @Delete TABLE
(
  id int
)
;WITH IdsToDelete (id)
AS (
  SELECT ChildStepId
  FROM StepInput
  WHERE StepId = @stepId
  UNION ALL
  SELECT s.ChildStepId
  FROM StepInput s
  INNER JOIN IdsToDelete I ON I.id = s.StepId
)

INSERT INTO @Delete (id)
  SELECT id
  FROM IdsToDelete

DELETE FROM StepInput WHERE StepId = @stepId OR StepId IN (SELECT Id FROM @Delete)
DELETE FROM Step WHERE StepId IN (SELECT Id FROM @Delete) OR StepId = @stepId

这个想法是创建一个递归cte，存储所有要删除的id，然后将它们存储到一个声明的表中，这样cte在第一次声明后就会丢失数据。

然后您只需要删除表上id所在的所有内容。我还删除了您对步骤表的约束，该约束导致所有删除都失败。

我希望这次我做对了，我希望你能理解我在那里做了什么。

干杯，