外键 - 他们为我做什么？ [英] Foreign Keys - What do they do for me?

问题描述

我正在构建一个小应用程序，并在表之间设置外键关系。但是我很困惑为什么我真的需要这个？有什么优点 - 它帮助我写我的查询时，我不必执行任何连接？以下是我的数据库的示例代码段：

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       |
+-------------------+

$ b b

用户， user_id 和项目之间有一个关键的关系。创建者

我可以执行这样的查询吗？

Would I be able to perform a query like so?

SELECT * FROM PROJECTS WHERE USERS.username =真实用户;

因为MySQL应该知道表之间的关系？如果不是，那么在数据库设计中外键的真正功能是什么？

Since MySQL should know the relationship between the tables? If not then what is the real function of Foreign keys in a database design?

推荐答案

外键提供引用完整性。外键列中的数据被验证 - 该值只能是已经存在于表&在外键中定义的列。它是非常有效的停止坏数据 - 有人不能输入任何他们想要的 - 数字，ASCII文本等。这意味着数据被规范化 - 重复的值已被识别并隔离到自己的表，所以没有更多的问题关于处理文本中的大小写敏感性...和值是一致的。

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.

您对某个用户的项目的查询无效 - 您引用了一个当没有对查询中的表的引用时，从 USERS 表中删除列，并且没有子查询用于获取该信息，然后将其链接到 PROJECTS 表。您真正想要使用的是：

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:

SELECT p.*
   FROM PROJECTS p
   JOIN USERS u ON u.user_id = p.creator
WHERE u.username = 'John Smith'

这篇关于外键 - 他们为我做什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！