MySQL 如果可以使用WHERE，为什么还需要使用外键？

Why do I need to use foreign key if I can use WHERE?

提问by shin

A beginners' question about foreign key in MySQL.

关于 MySQL 中外键的初学者问题。

In w3schoolit says,

在w3school它说，

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

And also there is WHERE,

还有WHERE，

WHERE id = page_id

So if I can use WHERE for linking the tables, what is the main purpose of having foreign key?

因此，如果我可以使用 WHERE 来链接表，那么使用外键的主要目的是什么？

回答by cletus

It's not strictly needed for the query, it's true. It exists for several reasons:

查询并不是严格需要的，这是真的。它的存在有几个原因：

1. As a constraint on the table to stop you inserting something that doesn't point to anything;
2. As a clue for the optimizer; and
3. For historical reasons where is was more needed.

1. 作为对表的约束，以阻止您插入不指向任何内容的内容；
2. 作为优化器的线索；和
3. 由于历史原因，更需要它的地方。

(1) is probably the important one of the three. This is called referential integrity. It means that if there is a value in a foreign key there will be a corresponding record with that value as a primary key in the parent table.

(1) 可能是三个中重要的一个。这称为参照完整性。这意味着如果外键中有一个值，那么在父表中就会有一个以该值作为主键的对应记录。

That being said, not all databases support referential integrity (eg MySQL/MyISAM tables) and those that do don't necessarily enforce it (for performance reasons).

话虽如此，并非所有数据库都支持参照完整性（例如 MySQL/MyISAM 表），而那些不一定强制执行（出于性能原因）。

回答by Adriaan Stander

The Foreign is used for referential integrity.

Foreign 用于参照完整性。

See An introduction to foreign keys and referential integrity in MySQL

请参阅MySQL 中的外键和参照完整性简介

回答by Quassnoi

So if I can use WHERE for linking the tables, what is the main purpose of having foreign key?

因此，如果我可以使用 WHERE 来链接表，那么使用外键的主要目的是什么？

Because WHEREclause is not limited to equijoins on foreign keys.

因为WHERE子句不限于外键上的 equijoins。

Say, if you have a table which describes price ranges and discounts, you use this complex condition to join the tables:

假设您有一个描述价格范围和折扣的表，您可以使用这个复杂的条件来连接这些表：

SELECT  *
FROM    Goods
JOIN    PriceRange
ON      PriceRange.Price =
        (
        SELECT  MAX(Price)
        FROM    PriceRange
        WHERE   PriceRange.Price <= Goods.Price
        )

You cannot link these table with a foreign key relationship, but you can easily join them.

你不能用外键关系链接这些表，但你可以很容易地加入它们。

See this entry in my blog for more details:

有关更多详细信息，请参阅我博客中的此条目：

• What is entity-relationship model?

• 什么是实体关系模型？

The pk-to-pk binding, though, is still important. A FOREIGN KEYcan assure you that the entitie you are linking are described by your relational model.

不过，pk 到 pk 的绑定仍然很重要。AFOREIGN KEY可以向您保证，您正在链接的实体是由您的关系模型描述的。

With a FOREIGN KEY-backed design, you cannot declare a relationship to an entity whose PRIMARY KEYis absent in the table that describes that entity.

使用FOREIGN KEY-backed 设计，您不能声明与PRIMARY KEY描述该实体的表中不存在的实体的关系。

SQL Servercan even take this fact into account and optimize the certain types of queries.

SQL Server甚至可以考虑这一事实并优化某些类型的查询。

Say, this query:

说，这个查询：

SELECT  f.*
FROM    t_foreign f
WHERE   f.pid IN
        (
        SELECT  id
        FROM    t_primary p
        )

will not even look into t_primaryif the FOREIGN KEYrelationship is defined between t_foreignand t_primary.

甚至不会考虑t_primary如果FOREIGN KEY关系之间定义t_foreign和t_primary。

See this article for more details:

有关更多详细信息，请参阅这篇文章：

• Constraints and the optimizer in SQL Server: FOREIGN KEY

• SQL Server 中的约束和优化器：FOREIGN KEY

回答by Garry Shutler

Maintaining referential integrityand indexing.

维护参照完整性和索引。

回答by Jim H.

The primary purpose of a WHERE clause is to limit the rows returned by the query. See SELECT Syntax.

WHERE 子句的主要目的是限制查询返回的行。请参阅SELECT 语法。

Primary key/Foreign key relationships maintain referential integrity and with proper indexing increase the performance of queries. (See Pete OHanlon's explanation, above and JOIN Types)

主键/外键关系保持参照完整性，并通过适当的索引提高查询的性能。（参见上面的 Pete OHanlon 的解释和JOIN Types）

回答by just somebody

the RESTRICT operator (WHERE) has nothing to do with referential constraints!

RESTRICT 运算符 (WHERE) 与引用约束无关！

quote from C. J. Date's Relational Database Dictionary

引自 CJ Date 的关系数据库词典

foreign keyLet R1and R2be relvars, not necessarily distinct, and let Kbe a key for R1. Let FKbe a subset of the heading of R2such that there exists a possibly empty sequence of attribute renamings that maps Kinto K'(say), where K'and FKcontain exactly the same attributes. Then FKis a foreign key

外键让R1和R2是相关变量，不一定是不同的，让K是R1的键。令FK是R2的标题的子集，使得存在一个可能为空的属性重命名序列，将K映射到K'（比如说），其中K'和FK包含完全相同的属性。那么FK就是外键

referential integrityLoosely, the rule that no referencing tuple is allowed to exist if the corresponding referenced tuple doesn't exist. More precisely, let FKbe some foreign key in some referencing relvar R2; let Kbe the corresponding key in the corresponding referenced relvar R1, and let K'be derived from Kas described under foreign key. Then the referential integrity rule requires there never to be a time at which there exists an FKvalue in R2that isn't the K'value for some (necessarily unique) tuple in R1at the time in question. R1and R2here are the referenced relvar and the referencing relvar, respectively, and the constraint between them is a referential constraint.

引用完整性松散地，如果相应的引用元组不存在，则不允许引用元组存在的规则。更准确地说，让FK是某个引用 relvar R2 中的某个外键；令K为对应引用的相关变量R1 中的对应键，并令K'如外键下所述从K派生。然后参照完整性规则要求永远不会有某个时间在R2中存在一个FK值而不是R1 中某些（必须是唯一的）元组的K'值在有问题的时候。 这里的R1和R2分别是被引用的relvar和被引用的relvar，它们之间的约束是一个引用约束。

Examples: In relvar SP, {S#}and {P#}are foreign keys corresponding to the keys {S#}and {P#}in relvars S and P, respectively. Note that the key in the referenced relvar that corresponds to a given foreign key is not required to be a primary key specifically.

示例： 在 relvar SP 中，{S#}和{P#}分别是对应于键{S#}和{P#}在 relvar S 和 P中的外键。请注意，与给定外键对应的引用相关变量中的键不需要专门为主键。

回答by Mark Ewer

I have another good reason to add the key relationships to your database. There are various code generators that use this information to generate an object model from your database. One notable pattern in common use is the ActiveRecord pattern. Without key relationships, the ActiveRecord pattern would not know how your database entities are related so it would generate a much less useful object model.

我还有一个很好的理由将关键关系添加到您的数据库中。有多种代码生成器使用此信息从您的数据库生成对象模型。常用的一种值得注意的模式是 ActiveRecord 模式。如果没有键关系，ActiveRecord 模式将不知道您的数据库实体是如何相关的，因此它会生成一个不太有用的对象模型。

Code generation is not appropriate for every software project. But, it is helpful on a large number of projects. If you aren't using code generation you owe it to yourself to at least look into it.

代码生成并不适合每个软件项目。但是，它对大量项目很有帮助。如果您不使用代码生成，那么您至少应该研究一下。

回答by shanthan

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

FOREIGN KEY 约束用于防止会破坏表之间链接的操作。

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

FOREIGN KEY 约束还可以防止将无效数据插入到外键列中，因为它必须是它指向的表中包含的值之一。

回答by Pete OHanlon

A Foreign Key is used to maintain referential integrity, while a WHERE clause is used to join tables together in a SQL operation such as a select. The where clause can operate across multiple tables, but it is purely there as a filter.

外键用于维护参照完整性，而 WHERE 子句用于在 SQL 操作（例如选择）中将表连接在一起。where 子句可以跨多个表操作，但它纯粹是作为过滤器存在的。

Strictly speaking, you can get away without referential integrity, but it's not a good idea. Without referential integrity, you end up relying on your client application not incorrectly deleting or updating something at one end of a relationship chain that would have a knock on effect on the data, e.g. changing a key value to point to one that's not there.

严格来说，您可以在没有参照完整性的情况下逃脱，但这不是一个好主意。如果没有参照完整性，您最终将依赖于您的客户端应用程序不会错误地删除或更新关系链一端的某些内容，这会对数据产生影响，例如将键值更改为指向不存在的值。

Referential integrity is a great way of ensuring that related data is held in a consistent way.

参照完整性是确保以一致方式保存相关数据的好方法。

回答by solairaja

First of all. Good Question !!

首先。好问题 ！！

MySql is an RDBMS - Relational DBMS, so all the entities (tables) are related by an column.

MySql 是一个 RDBMS - 关系 DBMS，所以所有的实体（表）都通过一个列相关联。

EMPLOYEE - EMPID EMPNAME DEPTID

员工 - EMPID EMPNAME DEPTID

DEPARTMENT - DEPTID DEPTNAME

部门 - 部门名称

DEPTID is foriegn key in the EMPLOYEE table and primary key in the DEPARTMENT table.

DEPTID 是 EMPLOYEE 表中的外键和 DEPARTMENT 表中的主键。

This relation is imaginary relation of objects just an consideration or kind of designing for structuring data in a easy way to retrieve in future. NOT A PHYSICAL RELATION (because its a programming language)

这种关系是对象的假想关系，只是一种考虑或一种设计，用于以一种易于将来检索的方式构造数据。不是物理关系（因为它是一种编程语言）

In order to retrive that data, we need few syntax and described by the Creator of SQL.

为了检索该数据，我们需要很少的语法并由 SQL 创建者描述。

SELECT * from EMPLOYEE

从员工中选择 *

SELECT * FROM DEPARTMENT

选择 * 从部门

SELECT * FROM EMPLOYEE WHERE DEPTID = 5

SELECT * FROM EMPLOYEE WHERE DEPTID = 5

Here we have realted the two tables imaginary for our convinent, but for the required result we used this syntax WHERE DEPTID = 5.

在这里，我们已经为我们的方便而虚构了两个表，但是对于所需的结果，我们使用了这种语法 WHERE DEPTID = 5。