c# - SQL可能会造成循环或多级联路径

Question

我编辑了整个问题，因为我设法找到了触发错误的原因，所以我只保留了 SQL 方法，因为它很容易测试

may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

DROP TABLE dbo.ProductionUnits
CREATE TABLE ProductionUnits
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(50)
)

DROP TABLE Cells
CREATE TABLE Cells
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(10),
    ProductionUnitId INT FOREIGN KEY REFERENCES ProductionUnits(Id) ON DELETE CASCADE ON UPDATE CASCADE 
)

DROP TABLE CheckLists
CREATE TABLE CheckLists
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(20),
    CellId INT FOREIGN KEY REFERENCES Cells(Id) ON DELETE CASCADE ON UPDATE CASCADE
)

DROP TABLE CheckListGroups
CREATE TABLE CheckListGroups
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(20),
    CheckListId INT FOREIGN KEY REFERENCES CheckLists(Id) ON DELETE CASCADE ON UPDATE CASCADE
)

DROP TABLE CheckListGroupItems
CREATE TABLE CheckListGroupItems
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(20),
    CheckListGroupId INT FOREIGN KEY REFERENCES CheckListGroups(Id) ON DELETE CASCADE ON UPDATE CASCADE
)


DROP TABLE Shifts
CREATE TABLE Shifts
(
    Id INT PRIMARY KEY IDENTITY,
    StartTime TIME,
    EndTime TIME,
    ShiftDescription NVARCHAR(20),
    ProductionUnitId INT FOREIGN KEY REFERENCES ProductionUnits(Id) ON DELETE CASCADE ON UPDATE CASCADE
)

DROP TABLE ProductionRecords
CREATE TABLE ProductionRecords
(
    Id INT PRIMARY KEY IDENTITY,
    CreatedOn DATETIME,
    CreatedBy NVARCHAR(50),
    ModifiedOn DATETIME,
    ModifiedBy NVARCHAR(50)
)

DROP TABLE dbo.Referencias
CREATE TABLE Referencias
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(15),
)

DROP TABLE dbo.CheckListRecords
CREATE TABLE CheckListRecords
(
    Id INT PRIMARY KEY IDENTITY,
    Value NVARCHAR(3),
    ReferenciaId INT FOREIGN KEY REFERENCES Referencias(Id) ON DELETE CASCADE ON UPDATE CASCADE,
    ShiftId INT FOREIGN KEY REFERENCES Shifts(Id) ON DELETE CASCADE ON UPDATE CASCADE,
    CheckListGroupItemId INT FOREIGN KEY REFERENCES dbo.CheckListGroupItems(Id) ON DELETE CASCADE ON UPDATE CASCADE,
    ProductionRecordsId INT FOREIGN KEY REFERENCES ProductionRecords(Id) ON DELETE CASCADE ON UPDATE CASCADE 
)   

所以问题是当我添加

CheckListGroupItemId INT FOREIGN KEY REFERENCES dbo.CheckListGroupItems(Id) ON DELETE CASCADE ON UPDATE CASCADE,

但是我需要这个来知道属于哪个项目但是如果我删除 ON DELETE CASCADE 就可以了

当涉及到外键和处理级联路径时，我对 SQL 不是很有经验-

遇到这种情况我该怎么办？

Answer 1

当您删除 ProductionUnits 行时，有两种删除 CheckListRecords 的方法。这就是它所提示的。

ProductionUnits ->
 Cells ->
  CheckLists ->
   CheckListGroups ->
    CheckListGroupItems ->
     CheckListRecords

...和...

ProductionUnits ->
 Shifts ->
  CheckListRecords

真可惜 - 你不能拥有它......而且没有真正简单的方法来解决它 :-(

这是常有的事。但是，抱有希望 - 有一种合理的方法来处理它......这并不太糟糕。当你收到这样的消息时，从它正在提示的表中向后工作......当你在删除路径中找到多个指向同一个表的 ON DELETE CASCADE 列时，你'我找到了你的罪魁祸首。

在我提出解决方案之前，请先观察几点:一方面，您拥有由身份生成的 ID。因此，On Update Cascade 可能不应该在这样的世界中指定，因为 ID 不能轻易更改(除非您在某处表现非常糟糕)。

另一个小的最佳实践有点注意:在创建表时指定模式:

CREATE TABLE dbo.ProductionUnits
(
   -- etc.
)

...并且总是在 View 和过程中引用表时使用模式。

好的 - 那么如何处理冲突呢？您可能会考虑将外键从 CheckListRecords 移除到 Shifts，然后在 Shifts 上实现 delete trigger> 删除受影响的 CheckListRecords 行。很久以前……在声明性参照完整性出现之前，您必须处理与触发器的关系管理。它在这样的地方仍然派上用场。

假设您已将模式添加到表声明中，它大致类似于:

create trigger [Shifts.Delete.Trigger] on dbo.Shifts for delete as
begin
  set nocount on;
  delete dbo.CheckListRecords where ShiftId in ( select Id from deleted );
end

看，这还不错，是吗？

如果您只是必须出于某种原因更新 Shifts.Id，它会变得有点复杂。为了实现逻辑，您必须在 Shifts 表上有一个备用键...最好是一个您永远不会更新的列，或者至少一个在 Id< 时不会更新的列 被更改。也许是 StartTime 列？如果您没有候选键，您可以添加一个 uniqueidentifier 列。例如:

CREATE TABLE Shifts
(
  Id INT PRIMARY KEY IDENTITY,
  StartTime TIME,
  EndTime TIME,
  ShiftDescription NVARCHAR(20) ,
  ProductionUnitId INT FOREIGN KEY REFERENCES ProductionUnits(Id) ON DELETE CASCADE ON UPDATE CASCADE,
  RowId UNIQUEIDENTIFIER 
    CONSTRAINT [Shifts.RowId.Default] DEFAULT ( NEWID() )
    CONSTRAINT [Shifts.RowId.Unique] UNIQUE
)

您的大部分观点和逻辑都会完全忽略此专栏。您甚至不需要让 EF 看到它。按照我的声明方式，它会在插入时自动设置(类似于标识列)。

唯一使用 RowId 的代码是更新触发器:

create trigger [Shifts.Update.Trigger] on dbo.Shifts for update as
begin
  set nocount on;
  if update( Id ) --> just skip everything if the Id did not change
  begin
    update dbo.CheckListRecords
    set ShiftId = i.Id
    from
      inserted i
      inner join
      deleted d
      on
        i.RowId = d.RowId
      inner join
      dbo.CheckListRecords c
      on
        c.ShiftId = d.Id        
  end
end

这不是 super 可怕……但并不理想。实际上，如果您没有任何理由更新 ID 列(而且您真的不应该更新)，您可以跳过所有这些更新触发器和多余的列废话。

我认为，如果您拥有基于触发器的参照完整性，则必须将其告知 EF……但 EF 不是我的人的方式……所以我无法解决这一点。但我几乎可以肯定，当您向 EF 描述您的数据模型时，这是一个相当简单的声明。