MySQL MySQL中外键的基础知识？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/757181/
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
Basics of Foreign Keys in MySQL?
提问by Macha
Is there any good explanation of how to use MySQL's foreign key construct?
关于如何使用 MySQL 的外键构造有什么好的解释吗？
I don't quite get it from the MySQL docs themselves. Up until now I've been handling things like foreign keys with joins and programming code.
我不太了解 MySQL 文档本身。到目前为止，我一直在处理诸如带有连接和编程代码的外键之类的事情。
And the second part of the question, are there any improvements to be made by using MySQL's inbuilt foreign keys?
问题的第二部分，使用 MySQL 的内置外键是否有任何改进？
回答by Quassnoi
FOREIGN KEYSjust ensure your data are consistent.
FOREIGN KEYS只需确保您的数据一致即可。
They do not improve queries in sense of efficiency, they just make some wrong queries fail.
他们并没有在效率意义上提高查询，他们只是让一些错误的查询失败。
If you have a relationship like this:
如果你有这样的关系：
CREATE TABLE department (id INT NOT NULL)
CREATE TABLE employee (id INT NOT NULL, dept_id INT NOT NULL, FOREIGN KEY (dept_id) REFERENCES department(id))
, then you cannot delete a departmentif it has some employee's.
, 那么你不能删除 adepartment如果它有 some employee。
If you supply ON DELETE CASCADEto the FOREIGN KEYdefinition, the referencing rows will be deleted automatically along with the referenced ones.
如果您提供ON DELETE CASCADE给FOREIGN KEY定义，则引用行将与引用行一起自动删除。
As a constraint, FOREIGN KEYactually slows down the queries a little.
作为约束，FOREIGN KEY实际上会稍微减慢查询速度。
Extra checking needs to be performed when deleting from a referenced table or inserting into a referencing one.
从引用表中删除或插入引用表时，需要执行额外检查。
回答by Chad Birch
The main benefits of using real foreign keys are ensuring data integrity, and being able to set up cascading actions on related items when something is modified or deleted.
使用真实外键的主要好处是确保数据完整性，并且能够在修改或删除某些内容时对相关项目设置级联操作。
For example, imagine you're programming a forum. You have a "topics" table with primary key topics.topic_id, and you have a "posts" table where posts are attached to topics with the column posts.topic_id, which is a foreign key to the topics table.
例如，假设您正在为一个论坛编程。您有一个带有主键的“主题”表，topics.topic_id还有一个“帖子”表，其中的帖子通过列附加到主题，列posts.topic_id是主题表的外键。
This foreign key relationship ensures that every post is attached to a valid topic. If the only topic you have has ID #1, it's impossible for there to exist a post in the database attached to topic #2. The database ensures this.
这种外键关系确保每个帖子都附加到一个有效的主题。如果您拥有的唯一主题 ID 为 #1，则数据库中不可能存在附加到主题 #2 的帖子。数据库确保了这一点。
For the cascading benefit, you can set it up so that if a topic is deleted from the topic table, the database automatically deletes all the posts in the posts table that were attached to this topic. This is nice because it removes a step that you have to remember to do manually, which can get quite complex when you have many tables linked together. With foreign keys all the relationships can be cleaned up automatically.
为了级联的好处，您可以将其设置为如果从主题表中删除主题，数据库会自动删除帖子表中附加到该主题的所有帖子。这很好，因为它删除了您必须记住手动执行的步骤，当您将许多表链接在一起时，这会变得非常复杂。使用外键可以自动清理所有关系。
回答by Gaurav Kumar
1.FOREIGN KEYS just ensure your data are consistent.
2. If we apply on delete cascade to the foreign key definition,referencing row will delete automatically when parent row will delete.
3. If we apply on Update Cascade to the foreign key definition,Child row will update automatically when parent row will update.
1.Foreign KEYS 只是确保您的数据是一致的。
2.如果我们将删除级联应用于外键定义，当父行删除时，引用行将自动删除。
3.如果我们对外键定义应用Update Cascade，当父行更新时，子行会自动更新。
Query:
ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ;
查询：ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ;
- you can not delete direct parent table , first delete foreign key from child table than delete parent table.
- 不能直接删除父表，先删除子表的外键，再删除父表。
回答by Seb
The main advantage is that you can limit which values you can enter in the table; if you try to enter a value that doesn't exist in the referenced table, you won't be able to do it.
主要优点是您可以限制可以在表中输入的值；如果您尝试输入引用表中不存在的值，您将无法执行此操作。
Also, if you update or delete the value in the referenced table, you can set it to automatically update the value or delete in cascade any row containing that value.
此外，如果您更新或删除引用表中的值，您可以将其设置为自动更新值或级联删除包含该值的任何行。
It's indeed a great feature leveraging your code.
利用您的代码确实是一个很棒的功能。
