在物化路径上强制引用完整性？
我正在尝试使用此处描述的物化路径模型来实现树状结构： http: //www.dbazine.com/oracle/or-articles/tropashko4。
是否可以在 [path] 字段上强制执行引用完整性？ 我不明白 SQL 如何做到这一点，我是否必须在 DAL 中手动执行它？
I'm trying to implement a tree like structure using a Materialized Path model described here: http://www.dbazine.com/oracle/or-articles/tropashko4.
Is it possible to enforce referential integrity on the [path] field? I don't see how SQL could do it, do I have to do it manually in the DAL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，当您对分层数据使用物化路径或嵌套集解决方案时，您必须自己在 DAL 中强制执行数据完整性。
邻接列表支持引用完整性，对于我称为“闭包表”（Tropashko 称这种设计为“传递闭包关系”）。
Yes, you have to enforce data integrity yourself in the DAL when you use either Materialized Path or Nested Sets solutions for hierarchical data.
Adjacency List supports referential integrity, and this is true also for a design I call "Closure Table" (Tropashko calls this design "transitive closure relation").
Vadim Tropashko 在那篇文章中提出的“物化路径”将顺序概念引入到关系中（“琼斯是第二个成员”）。
“物化路径”只不过是传递闭包上的“某种形式的物化视图”，因此遇到与任何其他“物化视图”完全相同的问题，只是由于涉及闭包，问题在算法上变得更糟。
当闭包约束发挥作用时，SQL 几乎完全无能为力。 （意思是：是的，SQL 要求你自己做所有事情。）这是 RM 展示其几乎无限能力的最大功能的领域之一，但 SQL 却失败得很厉害，而且令人遗憾的是，大多数人都错误地使用了 SQL因为有关系。
（@Bill Karwin：我希望能够给你+1，因为你对树的深度和性能结果之间的关系的评论。没有已知的算法来计算在以下情况下表现良好的闭包具有“疯狂”深度的树。这是一个算法问题，不是 SQL 也不是关系问题。）
编辑
是的，RM = 关系模型
"Materialized path" as presented by Vadim Tropashko in that article, introduces the notion of order into a relation ("Jones is the second member".).
"Materialized path" is nothing but "some form of materialized view" on the transitive closure, and therefore suffers all and exactly the same problems as any other "materialized view", except that matters are algorithmically worse precisely because of the involvement of a closure.
SQL is almost completely powerless when constraints-on-a-closure are in play. (Meaning : yes, SQL requires you to do everything yourself.) It's one of those areas where the RM shows the maximum of its almost unlimited power, but SQL fails abysmally, and where it is such a shame that the majority of people mistake SQL for being relational.
(@Bill Karwin : I'd like to be able to give you +1 for your remark on the relation between the depth of the trees and the result on performance. There are no known algorithms to compute closures that perform well in the case of trees with "crazy" depths. It's an algorithmic problem, not an SQL nor a relational one.)
EDIT
Yes, RM = Relational Model
在物化路径模型中，您可以使用任意字符串（可能是 unicode 字符串以允许超过 256 个子项）而不是“xyz”形式的特殊字符串。 父级的 id 就是直接子级的 id，删除了最后一个字符。 使用检查约束轻松强制执行此操作，例如（我的示例在 PostgreSQL 中工作）
您可以在创建表命令中 。 如果您坚持使用“xyz”形式的字符串，则必须使用正则表达式，但我猜有可能找到相应的检查约束。
In the materialized path model you can use arbitrary strings (maybe unicode strings to allow more than 256 children) instead of special strings of form "x.y.z". The id of the parent is then the id of the direct children with the last character removed. You can easily enforce this with a check constraint like (my example works in PostgreSQL)
within your create table command. If you insist on strings of form "x.y.z", you'll have to play around with regular expressions, but I'd guess it's possible to find a corresponding check constraint.
是的，我们可以“在 [path] 字段上强制引用完整性”。 我几年前就这样做了，如下所述：
将您的配置设置作为层次结构存储在数据库中
Yes, we can "enforce referential integrity on the [path] field". I did that a couple of years ago, described here:
Store your configuration settings as a hierarchy in a database