不理解 Sql Server 错误
我有一个用户表（User），需要创建一个新表来跟踪哪些用户推荐了其他用户。因此，基本上，我在同一个表中的行之间创建多对多关系。
因此，我尝试使用 UserId 和 UserReferredId 列创建表 UserReferrals。我将两列都设置为复合主键。两列都是链接到 User.UserID 的外键。
由于删除用户也应该删除关系，因此我将两个外键设置为级联删除。删除用户后，UserReferrals 中的任何相关行也应删除。
但这给了我消息：
'User'表保存成功 “UserReferrals”表无法创建关系“FK_UserReferrals_User”。在表“UserReferrals”上引入 FOREIGN KEY 约束“FK_UserReferrals_User”可能会导致循环或多个级联路径。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束。无法创建约束。请参阅以前的错误。
我没有收到此错误。级联删除只会删除带有外键的行，对吗？那么怎么会造成“循环级联路径”呢？
感谢您的任何提示。
I have a table of users (User), and need to create a new table to track which users have referred other users. So, basically, I'm creating a many-to-many relation between rows in the same table.
So I'm trying to create table UserReferrals with the columns UserId and UserReferredId. I made both columns a compound primary key. And both columns are foreign keys that link to User.UserID.
Since deleting a user should also delete the relationship, I set both foreign keys to cascade deletes. When the user is deleted, any related rows in UserReferrals should also delete.
But this gives me the message:
'User' table saved successfully
'UserReferrals' table Unable to create relationship 'FK_UserReferrals_User'. Introducing FOREIGN KEY constraint 'FK_UserReferrals_User' on table 'UserReferrals' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.
I don't get this error. A cascading delete only deletes the row with the foreign key, right? So how can it cause "cycling cascade paths"?
Thanks for any tips.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果表 (A) 上的 FK 引用了表 (B)，而表 (B) 又与表 (A) 存在关系，或者 FK 引用了同一个表中的 PK，则可能会出现循环场景。有时这在逻辑上是不可能的 - 但从纯粹的理论上来说，在 SQL 引擎看来这是可能的。
这是无法避免的。通常我们在 SP 中处理这些（在 EF 中我们可以映射到删除方法）。
If you have FK's on a table (A) that references a table (B) that in turn also has a relationship to (A), or an FK that references a PK in the same table, it can introduce a scenario where it cycles. Sometimes this isn't logically possible - but in pure theory it's possible in the eyes of the SQL engine.
This isn't avoidable. Typically we handle these in an SP (which in EF we can map to the delete method).
如果您允许级联删除，则删除其 UserId 出现在其他用户的 UserReferredId 字段中的人员将导致这些用户也被删除！我怀疑您想要的是将 UserReferredId 的值设置为 null 如果与其绑定的用户被删除。
有多种方法，从删除命令上的表触发器到使用存储过程进行删除。忽略触发器是邪恶的论点，可以创建类似的内容：
在删除后对用户创建触发器clearUserReferredIdOnUserDelete
作为
update users set UserReferredId = null where UserReferredId in (select userid from dead)
这未经测试，但应该接近。
磷
If you were to allow cascading deletes, the deletion of a person whose UserId appears in the UserReferredId field of other users would cause those user to be deleted too! I suspect what you want is to have the value of UserReferredId set to null if the User whom is tied to it is deleted.
There are several approaches ranging from a table trigger on the delete command to using a store procedure for your deletion. Ignoring the triggers are evil argument, one could create something like:
create trigger clearUserReferredIdOnUserDelete on Users after delete
as
update users set UserReferredId = null where UserReferredId in (select userid from deleted)
That's untested but should be close.
P
经过思考之后，我开始认为问题与其说与循环级联路径相关，不如说与多个级联路径相关。
尽管我的连接表中的两个 UserID 始终不同，但没有什么可以阻止它们相同。如果它们都引用同一个用户，并且该用户被删除，则将存在多个连接表的级联路径。
After thinking about this, I'm starting to think the problem is not so much related to cyclic cascade paths as much as it may be related to multiple cascade paths.
Although the two UserIDs in my joining table will always be different, nothing prevents them from being the same. If they both referred to the same user, and that user was deleted, there would be multiple cascade paths to the joining table.