SQL Server多级联路径问题-我的模型有问题吗？ [英] SQL Server multiple cascade path issue - is my model faulty?

问题描述

我有以下模型：

我希望删除时自动级联工作-既方便，保持数据一致，又在Entity Framework Code First中得到很好的支持。问题在于此特定模型会导致多个级联路径。当我尝试通过EF Code First生成数据库时，这导致SQL Server给我以下错误：

I want automatic cascade on delete to work - both because it's convenient, keep my data consistent and because it's well supported in Entity Framework Code First. The problem is that this specific model causes multiple cascade path. This results in SQL Server gives me the following error when I try to generate the database through EF Code First:

引入FOREIGN KEY约束'FK_dbo表'Event'上的.Events_dbo.Tournaments_Tournament_Id'可能
导致循环或多个级联路径。指定ON DELETE NO ACTION或
ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。

Introducing FOREIGN KEY constraint 'FK_dbo.Events_dbo.Tournaments_Tournament_Id' on table 'Event' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

我知道删除时锦标赛，无论是否删除参赛者或赛事触发删除 EventContestant 。 SQL Server对此不满意。

I understand that when deleting a Tournament, it would be a race whether or not the deletion of Contestant or Event triggers deletion of EventContestant. SQL Server doesn't appreciate that.

现在的问题是，我的模型有什么问题吗？无论如何，是否还有其他模型可以对此数据建模或可以进行一些配置以解决此问题？我想遵守以下要求：

Now the question is, is there anything wrong with my model? Is there anyway else to model this data or some configuring I can do to fix this? I'd like to adhere to the following requirements:

1. 删除比赛必须级联到 Event ，参赛者和 EventContestant


2. 删除 Event 必须级联到 EventContestant


3. 删除参赛者必须级联到 EventContestant


4. 关系在Contestant和Event之间是可选的（因此多重性为0-*）

1. Deletion of a Tournament must cascade to Event, Contestant and EventContestant
2. Deletion of an Event must cascade to EventContestant
3. Deletion of a Contestant must cascade to EventContestant
4. A relation between Contestant and Event is optional (hence the 0-* multiplicity)

我可以通过在EventContestant中引入单独的主键来解决此问题，而不是两个导航属性的组合键，但是然后我将需求1打破3。还有其他建议吗？

I could solve the issue by introducing a separate primary key in EventContestant, instead of the combined key of the two navigational properties, but then I would break requirement 1 through 3. Any other suggestions?

推荐答案

现在的问题是，我的模型有问题吗？

Now the question is, is there anything wrong with my model?

不一定。菱形依赖关系在数据库建模中相当普遍。

Not necessarily. Diamond-shaped dependencies are fairly common in database modeling.

问题在于，MS SQL Server不支持针对此类依赖关系的自动引用操作，即使它们可以是从逻辑的角度来看，这是完全正确的情况。换句话说，您的工具比应有的限制更多。

The problem is that MS SQL Server doesn't support automatic referential actions on such dependencies, even though they can be a perfectly valid situation from the logical standpoint. In other words, your tool is more limiting than it should be.

您将必须使用触发器来实现级联。

这篇关于SQL Server多级联路径问题-我的模型有问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！