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

问题描述

我知道他们是我的问题，你如何链接他们，或者当你在不同的表中有相同的名字时，他们是自动链接的。
下面是一个例子：

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

解决方案

...你怎么链接他们还是自动链接，当你有不同的表中相同的名称。

这不是自动的，你必须添加一个外键约束 customer_id 列订单表。这可以在创建表的时候或者使用ALTER语句完成。检查文档的细节。正如其他人指出的那样，请注意两个表都需要是InnoDB表（MyISAM存储引擎不支持外键约束，否则将忽略它们）。

无论是否有FK约束，查询都是这样的：

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

FK约束可以保证ORDER表的CUSTOMER_ID列不能包含CUSTOMER表中不存在的值（可能为NULL），从而强制引用完整性。

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:

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.

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).

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 = ...

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屋！