可能有一个对其自身具有引用完整性的表吗？
是否可以有一个表，其自身具有循环引用完整性键？例如，如果我有一个名为 Container 的表，
ObjectId ParentId
1 1
2 1
3 2
ObjectId 1 会引用自身。 Id 2 和 3 引用它们各自的父代，它们也在同一个表中。不可能删除 3 而不删除 2，删除 2 而不删除 1，也不可能删除 1。
我知道我可以通过交叉引用表来完成同样的事情，例如，
ObjectId ContainerId
1 1
2 2
3 3
ContainerId ObjectId
1 1
2 1
3 3
但我很感兴趣第一种方法可以实现更多目标，因为它会消除可能不必要的表格。这可能吗？
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，自引用表很好。
它们是表示深度嵌套层次结构的经典方法。
只需设置从子列到父列的外键（因此，子列中的值必须存在于父列中）。
Yes, self referencing tables are fine.
They are the classical way to represent deeply nested hierarchies.
Just set a foreign key from the child column to the parent column (so, a value in the child must exist in the parent column).
我已经这样做过很多次了。但请注意，如果您确实管理数据层次结构，SQL 不擅长树状查询。一些 SQL 供应商有 SQL 扩展来帮助解决这个问题，但 Joe Celko 的“嵌套集”对此非常有用。您将在搜索中获得大量点击。
目前，我使用嵌套集方法和自引用“parentID”作为引用的快捷方式：
其余的是嵌套集查询。
I have done this many times. But be aware if you really are managing hierachies of data, SQL isn't good at tree-like queries. Some SQL vendors have SQL extensions to help with this that might be usable, but Joe Celko's 'Nested Sets' is the cat's meow for this. You'll get lots of hits in a search.
Currently I use the nested-sets approach with a self-reference 'parentID' as a short-cut for the references:
The rest are nested-sets queries.
第一种方法有效，但是如果您尝试存储任意深度的树，则递归查询会很慢。您可以考虑存储邻接列表或不同的方法（请参阅http://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/）。
我们做的一件事是存储（在单独的表中）每个对象及其所有后继者，并在主表中拥有一个“父”指示符，我们用它来在应用程序中构建树。
The first way works, however if you're trying to store an arbitrarily deep tree, the recursive queries will be slow. You could look into storing an adjacency list or a different method (see http://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/).
One thing we do is to store (in a separate table) each object along with all of its successors as well as having a "parent" indicator in the main table, which we use to build the tree in the application.
George，我们的目标不是在使用自引用嵌套集方法时消除不必要的表。相反，它是为了处理一个事先不知道其深度的层次结构：你的老板的老板的老板的老板。谁知道组织树有多深？如果您确实提前知道层次结构的深度，并且不会频繁更改，那么最好使用单独的表，因为最好避免编写针对嵌套集的查询。简单性优于复杂性。
The goal, George, is not to eliminate an unnecessary table when using the self-reference nested set approach. Rather, it is to handle a hierarchy whose depth is not known in advance: your boss's boss's boss's boss. Who knows how deep that organizational tree may go? If you do know the depth of the hierarchy in advance, and it is not subject to frequent change, you would be better served with separate tables because writing queries against nested sets is a headache best avoided. Simplicity is better than complexity.