外键约束可能导致循环或多个级联路径？ [英] Foreign key constraint may cause cycles or multiple cascade paths?

问题描述

当我尝试添加约束到我的表时，我有一个问题。我收到错误：

在表'Employee'上引入FOREIGN KEY约束'FK74988DB24B3C886'可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION或修改其他FOREIGN KEY约束。

我的约束在代码表和 employee 表。 代码表包含 Id ，名称， FriendlyName ，键入和 Value 。 employee 有多个引用代码的字段，因此可以为每种类型的代码提供引用。

<如果所引用的代码被删除，我需要将字段设置为null。

任何想法如何做到这一点？

解决方案

SQL Server对级联路径进行简单计数，而不是试图确定是否有任何循环实际存在，它假设最坏的情况，拒绝创建引用操作（CASCADE）：您可以并且应该仍然创建没有引用操作的约束。

FWIW解决级联路径是一个复杂的问题，如果你不能改变你的设计（或者这样做会危及事情） 。其他SQL产品将简单地忽略该问题，并允许您创建循环，在这种情况下，将是一个竞争，看看将覆盖最后的值，可能是设计师的无知（例如ACE / Jet这样做）。我理解一些SQL产品将尝试解决简单的情况。事实上，SQL Server甚至不尝试，通过不允许多个路径播放它超安全，至少它告诉你这样。

I have a problem when I try to add constraints to my tables. I get the error:

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

My constraint is between a Code table and an employee table. The Code table contains Id, Name, FriendlyName, Type and a Value. The employee has a number of fields that reference codes, so that there can be a reference for each type of code.

I need for the fields to be set to null if the code that is referenced is deleted.

Any ideas how I can do this?

解决方案

SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (CASCADE): you can and should still create the constraints without the referential actions. If you can't alter your design (or doing so would compromise things) then you should consider using triggers as a last resort.

FWIW resolving cascade paths is a complex problem. Other SQL products will simply ignore the problem and allow you to create cycles, in which case it will be a race to see which will overwrite the value last, probably to the ignorance of the designer (e.g. ACE/Jet does this). I understand some SQL products will attempt to resolve simple cases. Fact remains, SQL Server doesn't even try, plays it ultra safe by disallowing more than one path and at least it tells you so.

这篇关于外键约束可能导致循环或多个级联路径？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！