database 您应该将自引用表列设为外键吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/246477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Should you make a self-referencing table column a foreign key?
提问by meleyal
For example to create a hierarchy of categories you use a column 'parent_id', which points to another category in the same table.
例如，要创建类别层次结构，您可以使用“parent_id”列，该列指向同一表中的另一个类别。
Should this be a foreign key? What would the dis/advantages be?
这应该是外键吗？缺点/优点是什么？
采纳答案by Mikezx6r
Yes. Ensures that you don't have an orphan (entry with no parent), and depending on usage, if you define a cascading delete, when a parent is deleted, all its children will also be deleted.
是的。确保您没有孤儿（没有父项的条目），并且根据使用情况，如果您定义级联删除，则删除父项时，其所有子项也将被删除。
Disadvantage would be a slight performance hit just like any other foreign key.
缺点是会像任何其他外键一样轻微影响性能。
回答by Guido
Yes, you should. If you have an attribute in a relation of database that serves as the primary key of another relation in the same database you should make it a FK.
是的你应该。如果数据库关系中有一个属性作为同一数据库中另一个关系的主键，则应将其设为 FK。
You will enjoy the advantages associated to foreign keys:
- Assuming the proper design of the relationships, foreign key constraints make it more difficult for a programmer to introduce an inconsistency into the database.
- Centralizing the checking of these constraints by the database server makes it unnecessary to perform these checks on the application side. This eliminates the possibility that different applications may not check constraints in the same way.
- Using cascading updates and deletes can simplify the application code.
- Properly designed foreign key rules aid in documenting relationships between tables.
- 假设关系设计正确，外键约束使程序员更难将不一致性引入数据库。
- 由数据库服务器集中检查这些约束使得不必在应用程序端执行这些检查。这消除了不同应用程序可能不会以相同方式检查约束的可能性。
- 使用级联更新和删除可以简化应用程序代码。
- 正确设计的外键规则有助于记录表之间的关系。
The disadvantages:
缺点：
- If you define Foreign Keys, sometimes it is harder to perform bulk operations.
- Maybe it implies more disk usage and a slight performance hit.
- 如果定义外键，有时执行批量操作会更困难。
- 也许这意味着更多的磁盘使用和轻微的性能损失。
回答by Tony Andrews
Yes you should.
是的你应该。
Advantages (as for any foreign key):
优点（对于任何外键）：
- Ensures that parent_id references a real row in the table
- Prevents accidental deletion of a parent that has children, or ensures that the delete cascades to delete the children also
- Provides information the optimizer can use
- 确保 parent_id 引用表中的真实行
- 防止意外删除有子级的父级，或确保删除级联也删除子级
- 提供优化器可以使用的信息
I can't think of any real disadvantages.
我想不出任何真正的缺点。
回答by Galwegian
Yes, you should make it a foreign key.
是的，您应该将其设为外键。
The benefits will be a better data model with less redundancy.
好处将是具有更少冗余的更好的数据模型。
