外键——它们对我有什么作用? [英] 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       |
+-------------------+

users.user_id和projects.creator

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

Would I be able to perform a query like so?

SELECT * FROM PROJECTS WHERE USERS.username = "a real user";

既然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.

您对用户拥有的项目的查询不起作用 - 当查询中没有对表的引用并且没有子查询用于在将其链接到 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屋！