为什么我需要使用外键如果我可以使用WHERE？ [英] Why do I need to use foreign key if I can use WHERE?

问题描述

在

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

还有WHERE，

  WHERE id = page_id 
  

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

解决方案

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

1. 作为表的一个约束来阻止你插入一些不指向任何东西的东西;
>

2. 作为优化器的线索;和
   
3. 出于历史原因，更需要的是
   
   $ b $（1）重要的三个之一。这被称为参照完整性。这意味着如果在外键中有一个值，那么在父表中将有一个相应的记录作为主键。
   
   
   

   就是说，不是所有的数据库都支持引用完整性（例如MySQL / MyISAM表），而那些不需要强制执行的数据库（出于性能原因）。
   

   A beginners' question about foreign key in MySQL.

   In w3school it says,

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

   And also there is WHERE,

   WHERE id = page_id
   

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

   解决方案

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

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

   这篇关于为什么我需要使用外键如果我可以使用WHERE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！