我应该使用外键在 SQL 中显示树关系吗
我正在尝试在表中建立树关系模型。 例如，有“类别”，类别本身可以位于父类别内。
我的架构是：
id int PRIMARY KEY,
parent_id int,
name
我的问题是，我应该将parent_id 列标记为外键吗？ 外国意味着“外部”，而不是自我指涉。 是否有用于此目的的不同类型的密钥？
我的问题类似于： MS SQL 中的自引用约束，但我问一个不同的问题，级联不是问题。
I am trying to model a tree relationship in a table. For instance, there are "Categories" and categories can themselves be inside a Parent category.
My schema is:
id int PRIMARY KEY,
parent_id int,
name
My question is, should I label the parent_id column as a Foreign key? Foreign implies "outside" and not self-referencing. Is there a different type of key for this purpose?
My question is similar to:
Self-referencing constraint in MS SQL, but I'm asking a different question, cascading not being an issue.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果嵌套级别非常深，那么高效地选择特定节点的所有后代可能并不容易，因为大多数数据库不能很好地处理递归。 另一种方法是使用所谓的“嵌套集模型”来表示关系。 这里有一篇很棒的文章：
http://www.intelligententerprise.com/001020/celko.jhtml< /a>
If you have very deep levels of nesting it may not be easy to performantly select out all descendants of a particular node, since most DB's do not handle recursion very well. Another approach is to use what's called the "Nested Set Model" to represent the relationships. A great article is available here:
http://www.intelligententerprise.com/001020/celko.jhtml
将树结构映射到关系数据库时，经常使用同一表中两列之间的外键。 然而，这并不是唯一可用的方法。
请参阅本文以获取替代说明：在数据库中存储分层数据
A foreign key between two columns in the same table is often used when mapping tree structures to relational databases. However, it is not the only approach avaiable.
See this article for alternative reperesentations: Storing Hierarchical Data in a Database
我不相信还有另一种类型的键...在这种情况下外键就可以了..它将强制对parent_id进行约束以确保它引用有效的id
I don't believe there is another type of key... a foreign key would be fine in this scenario.. it would enforce the constraint against the parent_id to ensure it references a valid id
自引用外键总是发生。 例如，一名员工可能有另一个“员工”作为他的经理，因此 manager_id 将是同一表中的 employee_id 字段的外键。
外键是表示分层数据中父节点的自然候选者，尽管它们并不专门用于此目的:)
Self-referencing foreign keys happen all the time. E.g. an employee might have another "employee" as his manager, so the manager_id will be a foreign key to the employee_id field in the same table.
Foreign keys are the natural candidate for representing the parent node in hierarchical data, although they're not exclusively used for that :)