解析循环和多个级联路径 [英] Resolve Cycles and Multiple Cascade Paths

问题描述

我有一组表，目前正在尝试为这些表设置正确的关系.精简版如下.

I have a set of tables and am currently trying to set up the correct relationships for the tables. The condensed version is below.

Users
    ID INT NOT NULL

Activities
    ID INT NOT NULL
    UserID INT NULL

Logs
    ID INT NOT NULL
    UserID INT NULL
    ActivityID INT NULL

我将 UserID 从 Activities 和 Logs 关联回 Users.ID 和 ActivityID 与 Activities.ID 相关.

I have relationships relating UserID from both Activities and Logs back to Users.ID and ActivityID relating back to Activities.ID.

我已将 Activities.UserID 和 Logs.UserID 设置为在删除时设置为 null，并在更新时设置级联.当我尝试将相同的更新和删除函数设置为 Logs.ActivityID 时，我的问题就出现了，但我收到一个关于可能导致循环或多个级联路径"的错误.我的问题是，Logs 需要一个用户，并且不需要 Activity，但是，Logs 确实有一个 Activity 需要在 Activity 发生变化时更新.

I have set Activities.UserID and Logs.UserID to set null on delete, and cascade on update. My problem comes when i attempt to set the same update and delete functions to Logs.ActivityID but i get an error about "may cause cycles or multiple cascade paths". My problem is, Logs Require a User, and do not require an Activity, but, Logs that do have an Activity need to be updated if and when an Activity changes.

我有什么方法可以不涉及两个单独的 Logs 表，并且不涉及手动更新 Logs 表.这甚至可以在 SQL Server 2012 中实现吗?

What way do i have around this that does not involve having two separate Logs tables, and does not involve manually updating the Logs table. Is this even possible in SQL Server 2012?

推荐答案

SQL Server 不支持多个级联路径.解决此限制的选项有:

SQL Server does not support multiple cascade paths. Your options to work around this limitation are:

1. 编写自己的逻辑来处理多个级联路径(推荐).

1. write your own logic for dealing with multiple cascade paths (recommended).

更改您的架构，以便不需要多个级联路径.

change your schema such that multiple cascade paths are not required.

等待 SQL Server 修复此问题.不要屏住呼吸 - 这已经是多年的限制.查看这些 Connect 项目:

wait for SQL Server to fix this. Don't hold your breath - this has been a limitation for years. See these Connect items:

一个.http://connect.microsoft.com/SQLServer/feedback/details/126159/cascade-updates
b.http://connect.microsoft.com/SQLServer/feedback/details/307723/allow-multiple-cascade-paths-for-foreign-key-and-on-dalete-update-cascade

注意他们是如何一直说我们现在没有时间做这个；我们会在下一个版本中考虑它"?这不是销售软件的事情，因为人们通常对自己编写逻辑感到满意.

Notice how they keep saying "we don't have time for this now; we'll consider it for the next release"? This isn't the kind of thing that sells software, because people are satisfied - generally - with coding the logic themselves.

迁移到支持多级联路径的数据库平台(听起来你认为你有很多选择，但我不认为你有，我不知道你通过切换牺牲了什么，不是甚至计算移植您的架构和代码).

migrate to a database platform that supports multiple cascade paths (sounds like you think you have many options for this, but I don't think you do, and I don't know what you sacrifice by switching, not even counting porting your schema and code).

这篇关于解析循环和多个级联路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！