I'm building a small application and setting up foreign key relationships between tables. However I'm confused as to WHY I really need this? What is the advantage - does it assist me when writing my queries that I don't have to perform any joins? Here's an example snippet of my database:
我正在构建一个小型应用程序，并在表之间建立外键关系。但是我很困惑为什么我真的需要这个?优点是什么?在编写查询时，它是否帮助我编写不需要执行任何连接的查询?下面是我的数据库示例片段:
+-------------------+
| USERS |
+-------------------+
| user_id |
| username |
| create_date |
+-------------------+
+-------------------+
| PROJECTS |
+-------------------+
| project_id |
| creator |
| name |
| description |
+-------------------+
There is a key relationship between users.user_id and projects.creator
用户之间有一个关键的关系。user_id和projects.creator
Would I be able to perform a query like so?
我是否能够执行这样的查询?
SELECT * FROM PROJECTS WHERE USERS.username = "a real user";
从用户所在的项目中选择*。用户名=“真正的用户”;
Since MySQL should know the relationship between the tables? If not then what is the real function of Foreign keys in a database design?
既然MySQL应该知道表之间的关系?如果不是，那么在数据库设计中，外键的真正功能是什么?
5 个解决方案
#1
16
Foreign keys provide referential integrity. The data in a foreign key column is validated - the value can only be one that already exists in the table & column defined in the foreign key. It's very effective at stopping "bad data" - someone can't enter whatever they want - numbers, ASCII text, etc. It means the data is normalized - repeating values have been identified and isolated to their own table, so there's no more concerns about dealing with case sensitivity in text... and the values are consistent. This leads into the next part - foreign keys are what you use to join tables together.
外键提供引用完整性。外键列中的数据被验证——该值只能是外键中定义的表和列中已经存在的值。它在阻止“坏数据”方面非常有效——有些人不能输入他们想要的任何东西——数字、ASCII文本等等。它意味着数据是规范化的——重复的值已经被识别出来并隔离到他们自己的表中，因此对于处理文本中的大小写敏感性不再关心……这些值是一致的。这就引出了下一部分——外键是用来将表连接在一起的。
Your query for the projects a user has would not work - you're referencing a column from the USERS table when there's no reference to the table in the query, and there's no subquery being used to get that information before linking it to the PROJECTS table. What you'd really use is:
用户对某个项目的查询将不起作用——当查询中没有对该表的引用时，您正在从USERS表引用一个列，并且在将该表链接到projects表之前，没有使用子查询来获取该信息。你真正需要的是:
SELECT p.*
FROM PROJECTS p
JOIN USERS u ON u.user_id = p.creator
WHERE u.username = 'John Smith'
#2
4
Basically, they won't give you any more functionality. They stop any inserts or updates breaking the referential integrity of your data model.
基本上，他们不会给你更多的功能。它们阻止任何插入或更新破坏数据模型的引用完整性。
Another key aspect in my opinion is that they communicate the data model to any other developers using it. I have often looked at what foreign keys a table has to see how the data model fits together at a glance.
我认为另一个关键的方面是，他们将数据模型与使用它的任何其他开发人员进行通信。我经常查看表的外键，以了解数据模型是如何简单地组合在一起的。
#3
3
If you never do joins, you don't need foreign keys.
如果从不连接，则不需要外键。
Come to think of it, if you never do joins, you don't need a relational database! (little joke) Seriously, if you have more than one table, you'd better learn how to use joins, and how to design foreign keys into a schema.
想想看，如果您从不做连接，那么您不需要关系数据库!(开玩笑)说真的，如果您有多个表，您最好学习如何使用连接，以及如何将外键设计为模式。
As previous responders have said, foreign keys enforce referential integrity. Without referential integrity, joins produce mysterious results.
正如之前的应答者所说，外键强制引用完整性。没有参照完整性，连接产生神秘的结果。
My earlier response failed to note the real question behind the question. The question I answered was "why are there foreign keys in SQL schemas", and the answer is "in order to do joins". But rereading the the question, I'm understanding a much more subtle question, which is "why can't SQL do the joins for me, if it knows the linkages". This is a really good question. It deserves a better answer than the above.
我先前的回答没有注意到问题背后的真正问题。我回答的问题是“为什么SQL模式中有外键”，答案是“为了进行连接”。但是重新阅读这个问题，我理解了一个更微妙的问题，那就是“如果SQL知道链接，为什么它不能为我做连接”。这是一个很好的问题。它应该得到比上面更好的答案。
A language where the engine supplies the join conditions is possible. One need only look at the graphical query design tool in Microsoft Access. Once one has declared all the intertable relationships to Access, one can pull data from multiple tables without specifying the join conditions all over again. Access figures them out automatically.
引擎提供联接条件的语言是可能的。只需查看Microsoft Access中的图形查询设计工具即可。一旦声明了要访问的所有互表关系，就可以从多个表中提取数据，而无需再次指定连接条件。Access会自动识别它们。
If one builds a two table query in Access, and then switches to SQL view, one will see that Access has in effect created a join with a join condition. Such a capability is possible in character based languages as well, but SQL is not such a language.
如果您在Access中构建了两个表查询，然后切换到SQL视图，那么您将看到Access实际上创建了一个具有连接条件的连接。这种能力在基于字符的语言中也是可能的，但是SQL不是这样的语言。
I note in passing that many projects can belong to one user. So Users is the "reference table" in the above schema, not Projects. I expect the easier automatic navigation direction would be automatic lookup from a reference table, not the other way around.
我注意到，许多项目可以属于一个用户。因此，用户是上述模式中的“引用表”，而不是项目。我希望更容易的自动导航方向将是来自引用表的自动查找，而不是相反。
#4
2
Using a foreign key constraint can provide the following:
使用外键约束可以提供以下内容:
- Prevent the database containing inconsistent data by preventing mismatched keys
- 通过防止键不匹配，防止包含不一致数据的数据库
- Prevent the database containing inconsistent data by automatically deleting orphaned rows (with ON DELETE CASCADE)
- 通过自动删除孤立的行(带有删除级联)来防止包含不一致数据的数据库
- Serve to document to future developers which column is a foreign key to which
- 用于向未来的开发人员记录哪个列是哪个外键
Of course it's not mandatory to do any of those things, but likely to improve code quality over time. Being strict about thing is usually good - it leads to more errors in testing (and hence fewer in production)
当然，做这些事情并不是强制性的，但是随着时间的推移，可能会提高代码质量。对事物的严格要求通常是好的——这会导致测试中出现更多的错误(因此在生产中会减少)
#5
0
If each user belongs to exactly one project and each project belongs to exatctly one user then it is said that the tables have a one to one relationship and having a key relationship between users.user_id and projects.project_id is ok (though probably not standard).
如果每个用户只属于一个项目，而每个项目仅仅属于一个用户，那么表就会有一个一对一的关系，并且在用户之间有一个关键的关系。user_id和项目。project_id是ok的(尽管可能不是标准的)。
However, if a user can belong to many projects (or a project can have many users) then you have a one to many relationship and you need a foreign key.
但是，如果一个用户可以属于多个项目(或者一个项目可以有多个用户)，那么您就拥有一个到多个关系，并且您需要一个外键。
If projects can have many users and users can belong to many projects then you have a many to many relationship and you need a more sophisticated model.
如果项目可以有许多用户，用户可以属于许多项目，那么您就有许多到许多的关系，您需要一个更复杂的模型。
#1
16
Foreign keys provide referential integrity. The data in a foreign key column is validated - the value can only be one that already exists in the table & column defined in the foreign key. It's very effective at stopping "bad data" - someone can't enter whatever they want - numbers, ASCII text, etc. It means the data is normalized - repeating values have been identified and isolated to their own table, so there's no more concerns about dealing with case sensitivity in text... and the values are consistent. This leads into the next part - foreign keys are what you use to join tables together.
外键提供引用完整性。外键列中的数据被验证——该值只能是外键中定义的表和列中已经存在的值。它在阻止“坏数据”方面非常有效——有些人不能输入他们想要的任何东西——数字、ASCII文本等等。它意味着数据是规范化的——重复的值已经被识别出来并隔离到他们自己的表中，因此对于处理文本中的大小写敏感性不再关心……这些值是一致的。这就引出了下一部分——外键是用来将表连接在一起的。
Your query for the projects a user has would not work - you're referencing a column from the USERS table when there's no reference to the table in the query, and there's no subquery being used to get that information before linking it to the PROJECTS table. What you'd really use is:
用户对某个项目的查询将不起作用——当查询中没有对该表的引用时，您正在从USERS表引用一个列，并且在将该表链接到projects表之前，没有使用子查询来获取该信息。你真正需要的是:
SELECT p.*
FROM PROJECTS p
JOIN USERS u ON u.user_id = p.creator
WHERE u.username = 'John Smith'
#2
4
Basically, they won't give you any more functionality. They stop any inserts or updates breaking the referential integrity of your data model.
基本上，他们不会给你更多的功能。它们阻止任何插入或更新破坏数据模型的引用完整性。
Another key aspect in my opinion is that they communicate the data model to any other developers using it. I have often looked at what foreign keys a table has to see how the data model fits together at a glance.
我认为另一个关键的方面是，他们将数据模型与使用它的任何其他开发人员进行通信。我经常查看表的外键，以了解数据模型是如何简单地组合在一起的。
#3
3
If you never do joins, you don't need foreign keys.
如果从不连接，则不需要外键。
Come to think of it, if you never do joins, you don't need a relational database! (little joke) Seriously, if you have more than one table, you'd better learn how to use joins, and how to design foreign keys into a schema.
想想看，如果您从不做连接，那么您不需要关系数据库!(开玩笑)说真的，如果您有多个表，您最好学习如何使用连接，以及如何将外键设计为模式。
As previous responders have said, foreign keys enforce referential integrity. Without referential integrity, joins produce mysterious results.
正如之前的应答者所说，外键强制引用完整性。没有参照完整性，连接产生神秘的结果。
My earlier response failed to note the real question behind the question. The question I answered was "why are there foreign keys in SQL schemas", and the answer is "in order to do joins". But rereading the the question, I'm understanding a much more subtle question, which is "why can't SQL do the joins for me, if it knows the linkages". This is a really good question. It deserves a better answer than the above.
我先前的回答没有注意到问题背后的真正问题。我回答的问题是“为什么SQL模式中有外键”，答案是“为了进行连接”。但是重新阅读这个问题，我理解了一个更微妙的问题，那就是“如果SQL知道链接，为什么它不能为我做连接”。这是一个很好的问题。它应该得到比上面更好的答案。
A language where the engine supplies the join conditions is possible. One need only look at the graphical query design tool in Microsoft Access. Once one has declared all the intertable relationships to Access, one can pull data from multiple tables without specifying the join conditions all over again. Access figures them out automatically.
引擎提供联接条件的语言是可能的。只需查看Microsoft Access中的图形查询设计工具即可。一旦声明了要访问的所有互表关系，就可以从多个表中提取数据，而无需再次指定连接条件。Access会自动识别它们。
If one builds a two table query in Access, and then switches to SQL view, one will see that Access has in effect created a join with a join condition. Such a capability is possible in character based languages as well, but SQL is not such a language.
如果您在Access中构建了两个表查询，然后切换到SQL视图，那么您将看到Access实际上创建了一个具有连接条件的连接。这种能力在基于字符的语言中也是可能的，但是SQL不是这样的语言。
I note in passing that many projects can belong to one user. So Users is the "reference table" in the above schema, not Projects. I expect the easier automatic navigation direction would be automatic lookup from a reference table, not the other way around.
我注意到，许多项目可以属于一个用户。因此，用户是上述模式中的“引用表”，而不是项目。我希望更容易的自动导航方向将是来自引用表的自动查找，而不是相反。
#4
2
Using a foreign key constraint can provide the following:
使用外键约束可以提供以下内容:
- Prevent the database containing inconsistent data by preventing mismatched keys
- 通过防止键不匹配，防止包含不一致数据的数据库
- Prevent the database containing inconsistent data by automatically deleting orphaned rows (with ON DELETE CASCADE)
- 通过自动删除孤立的行(带有删除级联)来防止包含不一致数据的数据库
- Serve to document to future developers which column is a foreign key to which
- 用于向未来的开发人员记录哪个列是哪个外键
Of course it's not mandatory to do any of those things, but likely to improve code quality over time. Being strict about thing is usually good - it leads to more errors in testing (and hence fewer in production)
当然，做这些事情并不是强制性的，但是随着时间的推移，可能会提高代码质量。对事物的严格要求通常是好的——这会导致测试中出现更多的错误(因此在生产中会减少)
#5
0
If each user belongs to exactly one project and each project belongs to exatctly one user then it is said that the tables have a one to one relationship and having a key relationship between users.user_id and projects.project_id is ok (though probably not standard).
如果每个用户只属于一个项目，而每个项目仅仅属于一个用户，那么表就会有一个一对一的关系，并且在用户之间有一个关键的关系。user_id和项目。project_id是ok的(尽管可能不是标准的)。
However, if a user can belong to many projects (or a project can have many users) then you have a one to many relationship and you need a foreign key.
但是，如果一个用户可以属于多个项目(或者一个项目可以有多个用户)，那么您就拥有一个到多个关系，并且您需要一个外键。
If projects can have many users and users can belong to many projects then you have a many to many relationship and you need a more sophisticated model.
如果项目可以有许多用户，用户可以属于许多项目，那么您就有许多到许多的关系，您需要一个更复杂的模型。