MySQL 如何在 PHP 中使用外键

How to use foreign keys with PHP

提问by Please Delete me

So I understand how to create foreign keys and I know what is the purpose of the FK. But I have a problem in understanding How to use them. I asked a question regarding Foreign keys HERE(Click link)

所以我了解如何创建外键并且我知道 FK 的目的是什么。但是我在理解如何使用它们时遇到了问题。我在这里问了一个关于外键的问题（点击链接）

Here is what I made:

这是我做的：

CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (name) REFERENCES items(name),
  FOREIGN KEY (id) REFERENCES user(id)
);

Now my question is how do I make the most out of this using PHP? From the link above, people have suggested that it's good to use only one foreign key in the user_purchase table, but what if I want several columns? Why don't we use several foreign keys for different columns of the same table?

现在我的问题是如何使用 PHP 充分利用这一点？从上面的链接，人们建议在 user_purchase 表中只使用一个外键是好的，但是如果我想要多列怎么办？为什么我们不对同一个表的不同列使用多个外键？

I am using mysql and php. I would appreciate it if you could show some examples of how you use PHP with the tables which have foreign keys to get get information using MYSQL commands. I really need a thorough explanation.

我正在使用 mysql 和 php。如果您能展示一些示例，说明如何将 PHP 与具有外键的表一起使用以使用 MYSQL 命令获取信息，我将不胜感激。我真的需要一个彻底的解释。

I also need to understand the terms Normalization and Denormalization. I would appreciate if you could give some links which explain these terms in great detail with examples or if you have any suggestion for some great books for beginners in database design, implementation, etc, I would really appreciate.

我还需要了解规范化和非规范化这两个术语。如果您能提供一些链接，通过示例详细解释这些术语，或者如果您对一些适合数据库设计、实现等初学者的好书有任何建议，我将不胜感激。

Thanks a lot.

非常感谢。

回答by geomagas

Foreign key columns/constraints disambiguation

外键列/约束消歧

So I understand how to create foreign keys and I know what is the purpose of the FK. But I have a problem in understanding How to use them.

所以我了解如何创建外键并且我知道 FK 的目的是什么。但是我在理解如何使用它们时遇到了问题。

Assuming you are referring to the foreign key constraints, the short answer would be you just don't use them.

假设您指的是外键约束，简短的回答是您只是不使用它们。

And here comes the long one:

来了一个长的：

We are accustomed to refer to columns being foreign keysto other tables. Especially during the normalization process, phrases like "user_purchase.i_idis a foreign key to the itemstable"would be very common. While that's a perfectly valid way to describe the relationship, it can get a little fuzzy when we reach the implementation phase.

我们习惯于将列称为其他表的外键。特别是在规范化过程中，像“user_purchase.i_id是items表的外键”这样的短语会很常见。虽然这是描述关系的一种完全有效的方式，但当我们到达实施阶段时，它可能会变得有点模糊。

Suppose you have created your tables withoutthe FOREIGN KEYclauses:

假设你已经创建了自己的表没有的FOREIGN KEY条款：

CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
);

Notice that, relation-wise, the foreign key columnsare still implemented. There's a column that references the usertable (id) and another one that references the itemstable (i_id) -- let's put the namecolumn aside for a moment. Consider the following data:

请注意，关系的角度来看，外键列都还在实施。有一列引用user表 ( id)，另一列引用items表 ( i_id) -- 让我们暂时将name列放在一边。考虑以下数据：

  user              user_purchase    items
| id  username |    | id  i_id |    | i_id  name            price |
| 23  john     |    | 55   10  |    |  10   chocolate bar    3.42 |
| 55  mary     |    | 70   10  |    |  33   mobile phone    82.11 |
| 70  fred     |    | 70   33  |    |  54   toothpaste       8.67 |
                    | 55   10  |    |  26   toy car          6.00 |
                    | 70   26  |

The relation is there. It is implemented by means of the user_purchasetable, which holds information as to who bought what. If we were to query the database for a relevant report, we would do:

关系就在那里。它是通过user_purchase表格实现的，表格保存了关于谁买了什么的信息。如果我们要查询数据库以获取相关报告，我们会这样做：

select * from user_purchase p
join user u on (p.id=u.id)
join items i on (p.i_id=i.i_id)

And that's how we use the relation and the foreign key columnsinvolved.

这就是我们如何使用关系和涉及的外键列。

Now, what if we do:

现在，如果我们这样做：

insert into user_purchase (id,i_id) values (23,99)

Apparently, this is an invalid entry. Although there is a user with id=23, there's no item with i_id=99. The RDBMS would allow that to happen, because it doesn't know any better. Yet.

显然，这是一个无效的条目。虽然有一个用户id=23，但没有项目i_id=99。RDBMS 将允许这种情况发生，因为它不知道有什么更好的。然而。

That's where foreign key constraintscome into play. By specifying FOREIGN KEY (i_id) REFERENCES items(i_id)in the user_purchasetable definition, we essentially give the RDBMS a rule to follow: entries with i_idvalues that are not contained in the items.i_idcolumn are not acceptable. In other words, while a foreign key columnimplements the reference, a foreign key constraintenforces the referential integrity.

这就是外键约束发挥作用的地方。通过FOREIGN KEY (i_id) REFERENCES items(i_id)在user_purchase表定义中进行指定，我们实质上为 RDBMS 提供了一条要遵循的规则：具有i_id未包含在items.i_id列中的值的条目是不可接受的。换句话说，当外键列实现引用时，外键约束强制执行引用完整性。

Note, however, that the above selectwouldn't change, just because you defined a FK constraint. Thus, youdon't use FK constraints, the RDBMS does, in order to protect your data.

但是请注意，上述内容select不会改变，仅仅因为您定义了 FK 约束。因此，您不使用 FK 约束，RDBMS 会使用，以保护您的数据。

Redundancies

冗余

...what if I want several columns? Why don't we use several foreign keys for different columns of the same table?

...如果我想要几列怎么办？为什么我们不对同一个表的不同列使用多个外键？

Ask yourself: Why would you want that? If the two foreign keys are to serve the same purpose, the redundancy will eventually get you in trouble. Consider the following data:

问问自己：你为什么想要那个？如果两个外键用于相同的目的，冗余最终会给您带来麻烦。考虑以下数据：

 user_purchase                   items
| id  i_id  name           |    | i_id  name            price |
| 55   10   chocolate bar  |    |  10   chocolate bar    3.42 |
| 70   10   chocolate bar  |    |  33   mobile phone    82.11 |
| 70   33   mobile phone   |    |  54   toothpaste       8.67 |
| 55   10   toothpaste     |    |  26   toy car          6.00 |
| 70   26   toy car        |

What's wrong with this picture? Did user 55buy two chocolate bars, or a chocolate bar and a toothpaste? This kind of ambiguity can lead to a lot of effort to keep data in-sync, which would be unnecessary if we just kept one of the foreign keys. In fact, why not drop the namecolumn altogether, since it is implied by the relation.

这张图有什么问题？用户是否55购买了两根巧克力棒，还是一根巧克力棒和一根牙膏？这种歧义会导致大量的努力来保持数据同步，如果我们只保留一个外键，这将是不必要的。事实上，为什么不name完全删除该列，因为它是由关系隐含的。

Of course, we could resolve this by implementing a composite foreign key, by setting PRIMARY KEY(i_id,name)for the itemstable (or defining an extra UNIQUE(i_id,name)index, it doesn't realy matter) and then setting a FOREIGN KEY(i_id,name) REFERENCES items(i_id,name). This way, only (i_id,name) couples that exist in the itemstable would be valid for user_purchases. Apart from the fact that you would still have oneforeign key, this approach is totally unnecessary, provided that the i_idcolumn is already enough to identify an item (can't say the same for the namecolumn...).

当然，我们可以通过实现一个复合外键来解决这个问题，通过PRIMARY KEY(i_id,name)为items表设置（或定义一个额外的UNIQUE(i_id,name)索引，这并不重要）然后设置一个FOREIGN KEY(i_id,name) REFERENCES items(i_id,name). 这样，只有存在于items表中的 (i_id,name)对对user_purchases. 除了您仍然会有一个外键这一事实之外，这种方法是完全没有必要的，前提是该i_id列已经足以识别一个项目（对于该name列不能说相同......）。

However, there's no rule against using multiple foreign keys to a table. In fact, there are circumstances that demand such an approach. Consider a person(id,name)table and a parent(person,father,mother)one, with the following data:

但是，没有任何规则禁止对一个表使用多个外键。事实上，有些情况需要这种方法。考虑一个person(id,name)表和parent(person,father,mother)一个，具有以下数据：

 person             parent
| id  name    |    | person  father  mother |
| 14  John    |    |   21      14      59   |
| 43  Jane    |    |   14      76      43   |
| 21  Mike    |
| 76  Frank   |
| 59  Mary    |

Obviously, all three columns of the parenttable are foreign keys to person. Not for the same relation, though, but for three different ones: Since a person's parents are persons too, the two corresponding columns must reference the same table persondoes. Note, however, that the three fields not only canbut also have torefer different persons in the same parentrow, since noone is his own parent and noone's father is his mother as well.

显然，表的所有三列parent都是 的外键person。但是，不是针对相同的关系，而是针对三个不同的关系：由于一个人的父母也是人，因此两个对应的列必须引用同一个表person。但是请注意，这三个字段不仅可以而且还必须引用person同一parent行中的不同s ，因为没有人是他自己的父母，也没有人的父亲是他的母亲。

回答by Barmar

Foreign keys are used in joins. For instance, if you want to know the usernames that purchased a particular item, you would write:

外键用于连接。例如，如果您想知道购买特定商品的用户名，您可以这样写：

select u.username
from items i
join user_purchase up on i.i_id = up.i_id
join user u on u.id = up.id
where i.name = "Some product name"

They may also be used by the database engine itself. It can detect if you create a row in user_purchasewhose idor i_idcolumn doesn't match anything in the referenced column in the other table.

它们也可以由数据库引擎本身使用。如果你创建一排它可以检测出user_purchase其id或i_id列中的引用列中的其他表不匹配任何东西。

You should notreplicate the namecolumn in the user_purchasetable. The nameis just an attribute of the item, it's not specific to any particular purchase. If you need to get the name of the item that was purchased, join with the itemstable.

你应该不会复制name在列user_purchase表。在name仅仅的一个属性item，它不是针对任何特定的购买。如果您需要获取所购买商品的名称，请加入items表格。

回答by Shafeeque

Instead of reading so many links, just try to implement this in any simple project. I'm just explaining how we gonna use the above tables.

与其阅读这么多链接，不如尝试在任何简单的项目中实现这一点。我只是在解释我们将如何使用上面的表格。

Suppose you 3 users in usertable and 5 items in itemstable.

假设user表中有3 个用户，表中有 5 个项目items。

usertable

user桌子

id  |  username |  password 
 1      abc         123
 2      def         456
 3      qwe         987 

itemstable

items桌子

i_id  |  name   |  price 
 1      item 1    6
 2      item 2    8
 3      item 3    11 
 4      item 4    3
 5      item 5    14  

your user_purchasetable look like this

你的user_purchase桌子看起来像这样

CREATE TABLE user_purchase( i_id INT(11) NOT NULL, id INT(11) NOT NULL, FOREIGN KEY (i_id) REFERENCES items(i_id), FOREIGN KEY (id) REFERENCES user(id) );

CREATE TABLE user_purchase( i_id INT(11) NOT NULL, id INT(11) NOT NULL, FOREIGN KEY (i_id) REFERENCES items(i_id), FOREIGN KEY (id) REFERENCES user(id) );

There is no need of item name again in this table. So I have removed.

此表中不再需要项目名称。所以我已经删除了。

i_id |  id  
 1      1      
 1      2
 2      2      
 3      3      

In the above table we will get, user 1 has purchased item 1, user 2 has purchased item 1,item 2 and user 3 has purchased item 3.

在上表中我们将得到，用户 1 购买了商品 1，用户 2 购买了商品 1，商品 2，用户 3 购买了商品 3。

This is how normalization works. You can use MySQL JOIN for getting user name and item details

这就是规范化的工作原理。您可以使用 MySQL JOIN 获取用户名和项目详细信息

SELECT B.user_name,C.name AS item_name,C.price
FROM user_purchase A 
JOIN user B ON A.id = B.id
JOIN items C ON A.i_id = C.i_id

Here is foreign key use to join

这是加入的外键使用

A.id = B.id
A.i_id = C.i_id

回答by Jardo

You treet tables with foreign keys in php the same way, as if they had no foreign keys. Foreign keys are defined in the database and have (almost) nothing to do with php. The only thing you have to do in php is reacting to potential errors that can be returned by sql queries, which brake the foreign key constraint (typically DELETE queries).

你用同样的方式在 php 中用外键树表，就好像它们没有外键一样。外键在数据库中定义并且（几乎）与 php 无关。您在 php 中唯一要做的就是对 sql 查询可能返回的潜在错误做出反应，这会阻止外键约束（通常是 DELETE 查询）。

And for your database schema, you should drop column "name" from "table user_purchase". It is redundat.

对于您的数据库架构，您应该从“表 user_purchase”中删除列“name”。它是冗余的。

回答by Clart Tent

Just looking at the normalization/de-normalization point:

只看归一化/去归一化点：

Normalization is a process of trying to remove redundancy in your database - in your example the namefield in user_purchaseis redundant - I can find out the name of the item by looking it up in the itemstable using i_id.

规范化是一个尝试删除数据库中冗余的过程 - 在您的示例中，中的name字段user_purchase是多余的 - 我可以通过items使用i_id.

So if we were to look at normalizing user_purchasewe'd probably remove the namefield and use a JOINto retrieve that when we needed it. This would, of course, also mean we don't need the second FOREIGN KEYreference to items.

因此，如果我们要考虑规范化，user_purchase我们可能会删除该name字段并JOIN在需要时使用 a来检索它。当然，这也意味着我们不需要第二次FOREIGN KEY引用items.

De-normalization is basically going the opposite way - adding redundancy - usually done for performance reasons.

去规范化基本上是相反的——增加冗余——通常是出于性能原因。

However, in your example you might also consider de-normalization for business reasons too. For example you might decide it is important to store the product name as it was when the user actually purchased it (rather than what it's called now) - just in case you need to be able to re-print an invoice for example. However even in this case you wouldn't want the FOREIGN KEYback to items(as it would "break" if the product was re-named).

但是，在您的示例中，您也可能出于业务原因考虑去规范化。例如，您可能决定将产品名称存储为用户实际购买时的名称（而不是现在的名称）很重要 - 例如，以防万一您需要能够重新打印发票。但是，即使在这种情况下，您也不希望FOREIGN KEY返回items（因为如果产品重新命名，它会“中断”）。