有人可以解释MySQL外键吗 [英] Can someone explain MySQL foreign keys

问题描述

我知道我的问题是什么，你如何链接它们，或者当你在不同的表中有相同的名字时它们会自动链接.这是一个例子:

I know what they are my question is, how do you link them or are they automatically linked when you have identical names in different tables. Here is an example:

假设我有一个 [orders] 表和一个 [customer] 表.[orders] 表中的每一行都有一个 customer_id 编号，该编号与 [customer] 表中的 customer_id 相关联.那么如何通过参考订单来获取客户信息呢?什么是 sql 查询?

Say I have an [orders] table and a [customer] table. Each row in the [orders] table has a customer_id number which is associated with the customer_id in the [customer] table. So how do I get the customer information by referencing the order? What would be the sql query?

推荐答案

...如何链接它们，或者当您在不同的表中有相同的名称时它们会自动链接.

... how do you link them or are they automatically linked when you have identical names in different tables.

这不是自动的，你必须添加一个 外键约束.这可以在创建表时或使用 ALTER 语句完成.检查文档以获取详细信息.正如其他人指出的那样，请注意两个表都需要是 InnoDB 表(MyISAM 存储引擎不支持外键约束，会忽略它们).

This is not automatic, you have to add a foreign key constraint on the customer_id column of the order table. This can be done at the table creation time or using an ALTER statement. Check the documentation for the details. As others pointed out, note that both tables need to be InnoDB tables (foreign key constraints are not supported by the MyISAM storage engine that will ignore them).

无论有无 FK 约束，查询将类似于:

With or without a FK constraint, the query would be something like:

SELECT * 
FROM CUSTOMER C, ORDER O
WHERE C.ID = O.CUSTOMER_ID
AND O.ID = ...

FK 约束将仅"保证 ORDER 表的 CUSTOMER_ID 列不能包含 CUSTOMER 表中不存在的值(可能为 NULL 除外)，从而强制引用完整性.

A FK constraint would "just" guarantee that the CUSTOMER_ID column of the ORDER table cannot contain values that doesn't exist in the CUSTOMER table (except potentially NULL) and thus enforce referential integrity.

这篇关于有人可以解释MySQL外键吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！