如何避免SQL-Server中的循环关系？
我正在创建一个自相关表：
表 Item 列：
ItemId int - PK;
金额 - 不为空； 价格货币 - 使用 UDF 的计算列，根据项目祖先的金额检索值。
ParentItemId int - 可为 null，引用此表中的另一个 ItemId。
我需要避免循环，这意味着，兄弟姐妹不能成为其祖先的祖先，这意味着，如果 ItemId=2 ParentItemId = 1，则不应允许 ItemId 1 ParentItemId = 2。
我不知道在这种情况下最好的做法是什么。 我认为我应该添加一个从 UDF 或其他任何东西获取标量值的 CK。
编辑： 另一种选择是创建一个 INSTEAD OF 触发器，并将 ParentItemId 字段的更新放入 1 个事务中，并从 @@RowIdentity 中选择 Price 字段，如果失败则取消事务，但我更喜欢 UDF 验证。
任何想法都受到真诚的欢迎。
I am creating a self-related table:
Table Item columns:
ItemId int - PK;
Amount money - not null;
Price money - a computed column using a UDF that retrieves value according to the items ancestors' Amount.
ParentItemId int - nullable, reference to another ItemId in this table.
I need to avoid a loop, meaning, a sibling cannot become an ancestor of his ancestors, meaning, if ItemId=2 ParentItemId = 1, then ItemId 1 ParentItemId = 2 shouldn't be allowed.
I don't know what should be the best practice in this situation.
I think I should add a CK that gets a Scalar value from a UDF or whatever else.
EDIT:
Another option is to create an INSTEAD OF trigger and put in 1 transaction the update of the ParentItemId field and selecting the Price field from the @@RowIdentity, if it fails cancel transaction, but I would prefer a UDF validating.
Any ideas are sincerely welcomed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这肯定需要在数据库级别强制执行吗？
我只是问，因为我有这样的数据库（其中与此类似的表就像一个文件夹），并且我只确保在应用程序中设置正确的父/子关系。
Does this definitely need to be enforced at the database level?
I'm only asking as I have databases like this (where the table similar to this is like a folder) and I only make sure that the correct parent/child relationships are set up in the application.
像这样的检查并不容易实施，并且可能的解决方案可能会导致很多错误，并且问题可能比最初的问题更难。通常添加对用户输入的控制并防止读取数据的无限循环就足够了。
如果您的应用程序使用存储过程，没有 ORM，那么我会选择在 SP 中实现此逻辑。否则 - 在其他层处理它，而不是在数据库中处理
Checks like this is not easy to implement, and possible solutions could cause a lot of bugs and problems may be harder then initial one. Usually it is enough to add control for user's input and prevent infinite loop on read data.
If your application uses stored procedures, no ORM, than I would choose to implement this logic in SP. Otherwise - handle it in other layers, not in DB
在现实生活中，这是一个多大的问题？检测这些情况的成本可能很高（也许使用触发器）。事实上，当所有交易中只有一小部分会导致此问题时，每笔交易都可能会花费您大量的精力。
先想一想。
How big of a problem is this, in real life? It can be expensive to detect these situations (using a trigger, perhaps). In fact, it's likely going to cost you a lot of effort, on each transaction, when only a tiny subset of all your transactions would ever cause this problem.
Think about it first.
一个简单的技巧是强制 ParentItemId 小于 ItemId。这可以防止在这个简单的上下文中出现循环闭合。
但是，也有一个缺点 - 如果您出于某种原因需要删除/插入父级，则可能还需要按顺序删除/插入其所有子级。
同样，层次结构需要按顺序插入，并且您可能无法重新分配父级。
A simple trick is to force the ParentItemId to be less than the ItemId. This prevents loop closure in this simple context.
However, there's a down side - if you need for some reason to delete/insert a parent, you may need to delete/insert all of its children in order as well.
Equally, hierarchies need to be inserted in order, and you may not be able to reassign a parent.
经过测试，效果非常好：
Tested and works just great: