I'm working on an old web application my company uses to create surveys. I looked at the database schema through the mysql command prompt and thought the tables looked pretty solid. Though I'm not a DB guru I'm well versed in the theory behind it (having taken a few database design courses in my software engineering program).
我正在研究我公司用于创建调查的旧Web应用程序。我通过mysql命令提示符查看了数据库模式，并认为这些表看起来很稳固。虽然我不是数据库专家，但我非常精通其背后的理论（在我的软件工程专业中学习了一些数据库设计课程）。
That being said, I dumped the create statements into an SQL file and imported them in MySQL Workbench and saw that they make no use of any "actual" foreign keys. They'll store another table's primary key like you would with a FK but they don't declare it as one.
话虽这么说，我将create语句转储到一个SQL文件中并将它们导入MySQL Workbench，并发现它们不使用任何“实际”外键。它们将像使用FK一样存储另一个表的主键，但它们不会将其声明为一个。
So seeing how their DB is designed the way I would through what I know (minus the FK issue) I'm left wondering that maybe there's a reason behind it. Is this a case of lazy programming or could you get some performance gains by doing all the error check programmatically?
因此，看看他们的数据库是如何设计的，就像我所知道的那样（减去FK问题），我想知道可能背后的原因。这是一个懒惰编程的情况还是通过编程方式执行所有错误检查可以获得一些性能提升？
In case you'd like an example they basically have Surveys and a survey has a series of Questions. A question is part of a survey so it holds it's PK in a column. That's pretty much it but they use it everywhere.
如果您想要一个例子，他们基本上有调查，调查有一系列问题。一个问题是调查的一部分，所以它在一个专栏中保留了它的PK。这几乎就是它，但他们到处使用它。
I'd appreciate any insight :) (I understand that this question might not have a right/wrong answer but I'm looking more for some information on why they would do this as this system has been pretty solid ever since we started using it so I'm led to believe that these guys knew what they were doing)
我很感激任何见解:)（我知道这个问题可能没有正确/错误的答案，但我正在寻找更多关于为什么他们会这样做的信息，因为自从我们开始使用它以来这个系统一直很稳固所以我被引导相信这些家伙知道他们在做什么
6 个解决方案
#1
12
The original developers might have opted to use MyISAM or any other storage engine that does not support foreign key constraints.
原始开发人员可能选择使用MyISAM或任何其他不支持外键约束的存储引擎。
#2
4
MySQL only supports the defining of actual foreign key relationships on InnoDB tables, maybe yours are MyISAM, or something else?
MySQL只支持在InnoDB表上定义实际的外键关系，也许你的是MyISAM，还是别的？
More important is that the proper columns have indices defined on them (so the ones holding the PK of another table should be indexed). This is also possible in MyISAM.
更重要的是，正确的列具有在其上定义的索引（因此持有另一个表的PK的那些列应该被索引）。这在MyISAM中也是可能的。
#3
3
As general points; keys speed up reads (if they are applicable to the read taking place they help the optimizer) and slow down writes (because they add overhead to the tables).
作为一般要点;密钥加速读取（如果它们适用于读取发生它们帮助优化器）并减慢写入（因为它们增加了表的开销）。
In the vast majority of cases the improvement of speed for reading and maintenance of referential integrity outweighs the minor overhead they add to writes.
在绝大多数情况下，读取和维护引用完整性的速度的提高超过了它们为写入添加的微小开销。
This distinction has been blurred by cacheing, mirroring etc as so many reads on the very big sites don't actually hit the 'live' database - but this is not very relevant unless you are working for Amazon, Twitter or the like.
这种区别因缓存，镜像等而变得模糊，因为很多大型网站上的大量读取实际上都没有达到“实时”数据库 - 但除非您在亚马逊，Twitter等工作，否则这不是很相关。
#4
2
On uber large databases (the type that Teradata support) you find that they don't use Foreign keys. The reason is performance. Every time you write out to the database, which is often enough in a data warehouse you have the added overhead of having to check all the fk's on a table. If you already know it to be true, what's the point.
在超级大型数据库（Teradata支持的类型）上，您发现它们不使用外键。原因是表现。每次你写出数据库时，在数据仓库中经常就足够了，你需要检查一张桌子上的所有fk。如果你已经知道它是真的，重点是什么。
Good design on a small db would just mean you put them in, but there are performance gains to be had by leaving them out.
小型数据库上的良好设计只是意味着您将它们放入，但是通过将它们排除可以获得性能提升。
#5
0
You don't really have to use foreign keys.
你真的不必使用外键。
If you don't have them, data might became inconsistent and you won't be able to use cascade deletes and updates.
如果您没有它们，数据可能会变得不一致，您将无法使用级联删除和更新。
If you have them you might loose some of the users data due to the bug in your SQL statements that happens because of schema changes.
如果您拥有它们，由于模式更改导致SQL语句中的错误，您可能会丢失一些用户数据。
Some prefer to have them, some prefer life without them. There's no real advantages in either case.
有些人喜欢拥有它们，有些人更喜欢没有它们的生活。在任何一种情况下都没有真正的优势。
#6
0
Here is a real life instance where I'm not using a foreign key.
这是一个真实的实例，我没有使用外键。
I needed a way to store a parent child relationship where the child may not exist, and the child is an abstract class. Since the child could be of a few types, I use one field to name the type of the child and one field to list the id of the child. The application handles most of the logic.
我需要一种方法来存储父子关系，其中孩子可能不存在，孩子是抽象类。由于孩子可能是几种类型，我使用一个字段来命名孩子的类型，并使用一个字段列出孩子的id。该应用程序处理大部分逻辑。
I'm not sure if this was the best design decision, but it was the best I could come up with under the deadline. It's been working well so far!
我不确定这是否是最好的设计决定，但这是我在截止日期之前想出的最好的决定。到目前为止它一直运作良好！
#1
12
The original developers might have opted to use MyISAM or any other storage engine that does not support foreign key constraints.
原始开发人员可能选择使用MyISAM或任何其他不支持外键约束的存储引擎。
#2
4
MySQL only supports the defining of actual foreign key relationships on InnoDB tables, maybe yours are MyISAM, or something else?
MySQL只支持在InnoDB表上定义实际的外键关系，也许你的是MyISAM，还是别的？
More important is that the proper columns have indices defined on them (so the ones holding the PK of another table should be indexed). This is also possible in MyISAM.
更重要的是，正确的列具有在其上定义的索引（因此持有另一个表的PK的那些列应该被索引）。这在MyISAM中也是可能的。
#3
3
As general points; keys speed up reads (if they are applicable to the read taking place they help the optimizer) and slow down writes (because they add overhead to the tables).
作为一般要点;密钥加速读取（如果它们适用于读取发生它们帮助优化器）并减慢写入（因为它们增加了表的开销）。
In the vast majority of cases the improvement of speed for reading and maintenance of referential integrity outweighs the minor overhead they add to writes.
在绝大多数情况下，读取和维护引用完整性的速度的提高超过了它们为写入添加的微小开销。
This distinction has been blurred by cacheing, mirroring etc as so many reads on the very big sites don't actually hit the 'live' database - but this is not very relevant unless you are working for Amazon, Twitter or the like.
这种区别因缓存，镜像等而变得模糊，因为很多大型网站上的大量读取实际上都没有达到“实时”数据库 - 但除非您在亚马逊，Twitter等工作，否则这不是很相关。
#4
2
On uber large databases (the type that Teradata support) you find that they don't use Foreign keys. The reason is performance. Every time you write out to the database, which is often enough in a data warehouse you have the added overhead of having to check all the fk's on a table. If you already know it to be true, what's the point.
在超级大型数据库（Teradata支持的类型）上，您发现它们不使用外键。原因是表现。每次你写出数据库时，在数据仓库中经常就足够了，你需要检查一张桌子上的所有fk。如果你已经知道它是真的，重点是什么。
Good design on a small db would just mean you put them in, but there are performance gains to be had by leaving them out.
小型数据库上的良好设计只是意味着您将它们放入，但是通过将它们排除可以获得性能提升。
#5
0
You don't really have to use foreign keys.
你真的不必使用外键。
If you don't have them, data might became inconsistent and you won't be able to use cascade deletes and updates.
如果您没有它们，数据可能会变得不一致，您将无法使用级联删除和更新。
If you have them you might loose some of the users data due to the bug in your SQL statements that happens because of schema changes.
如果您拥有它们，由于模式更改导致SQL语句中的错误，您可能会丢失一些用户数据。
Some prefer to have them, some prefer life without them. There's no real advantages in either case.
有些人喜欢拥有它们，有些人更喜欢没有它们的生活。在任何一种情况下都没有真正的优势。
#6
0
Here is a real life instance where I'm not using a foreign key.
这是一个真实的实例，我没有使用外键。
I needed a way to store a parent child relationship where the child may not exist, and the child is an abstract class. Since the child could be of a few types, I use one field to name the type of the child and one field to list the id of the child. The application handles most of the logic.
我需要一种方法来存储父子关系，其中孩子可能不存在，孩子是抽象类。由于孩子可能是几种类型，我使用一个字段来命名孩子的类型，并使用一个字段列出孩子的id。该应用程序处理大部分逻辑。
I'm not sure if this was the best design decision, but it was the best I could come up with under the deadline. It's been working well so far!
我不确定这是否是最好的设计决定，但这是我在截止日期之前想出的最好的决定。到目前为止它一直运作良好！