如何使用PHP的外键

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（点击链接）

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.

非常感谢。

5 个解决方案

#1

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 keys to other tables. Especially during the normalization process, phrases like "user_purchase.i_id is a foreign key to the items table" 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是项目表的外键”之类的短语将非常普遍。虽然这是描述关系的一种非常有效的方式，但是当我们到达实施阶段时它会变得有点模糊。

Suppose you have created your tables without the FOREIGN KEY clauses:

假设您已创建没有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 columns are still implemented. There's a column that references the user table (id) and another one that references the items table (i_id) -- let's put the name column aside for a moment. Consider the following data:

请注意，在关系方面，仍然实现了外键列。有一个引用用户表（id）的列和另一个引用items表（i_id）的列 - 让我们把名称列放在一边。请考虑以下数据：

  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_purchase table, 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 columns involved.

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

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 constraints come into play. By specifying FOREIGN KEY (i_id) REFERENCES items(i_id) in the user_purchase table definition, we essentially give the RDBMS a rule to follow: entries with i_id values that are not contained in the items.i_id column are not acceptable. In other words, while a foreign key column implements the reference, a foreign key constraint enforces the referential integrity.

这就是外键约束发挥作用的地方。通过在user_purchase表定义中指定FOREIGN KEY（i_id）REFERENCES项（i_id），我们基本上为RDBMS提供了遵循的规则：不包含items.i_id列中未包含i_id值的条目。换句话说，当外键列实现引用时，外键约束强制引用完整性。

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

但请注意，上面的选择不会改变，只是因为您定义了FK约束。因此，您不使用RDBMS的FK约束来保护您的数据。

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 55 buy 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 name column altogether, since it is implied by the relation.

这张照片出了什么问题？用户55买了两个巧克力棒，巧克力棒和牙膏吗？这种模糊性会导致为保持数据同步而付出很多努力，如果我们只保留一个外键，这将是不必要的。实际上，为什么不完全删除名称列，因为关系暗示了它。

Of course, we could resolve this by implementing a composite foreign key, by setting PRIMARY KEY(i_id,name) for the items table (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 items table would be valid for user_purchases. Apart from the fact that you would still have one foreign key, this approach is totally unnecessary, provided that the i_id column is already enough to identify an item (can't say the same for the name column...).

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

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:

但是，没有规则禁止对表使用多个外键。事实上，有些情况需要这种方法。考虑一个人（id，name）表和父（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 parent table 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 person does. Note, however, that the three fields not only can but also have to refer different persons in the same parent row, since noone is his own parent and noone's father is his mother as well.

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

#2

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_purchase whose id or i_id column doesn't match anything in the referenced column in the other table.

它们也可以由数据库引擎本身使用。它可以检测您是否在user_purchase中创建了一行，其id或i_id列与另一个表中引用列中的任何内容都不匹配。

You should not replicate the name column in the user_purchase table. The name is 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 items table.

您不应该复制user_purchase表中的name列。该名称只是该项目的一个属性，并不特定于任何特定购买。如果您需要获取已购买商品的名称，请加入商品表。

#3

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 user table and 5 items in items table.

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

user table

用户表

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

items table

物品表

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_purchase table 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

#4

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中使用外键对表进行treet，就好像它们没有外键一样。外键在数据库中定义，并且（几乎）与php无关。你在php中唯一需要做的就是对sql查询可能返回的潜在错误做出反应，这会产生外键约束（通常是DELETE查询）。

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

对于您的数据库模式，您应该从“table user_purchase”中删除列“name”。这是多余的。

#5

Just looking at the normalization/de-normalization point:

只看正常化/反规范化点：

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

规范化是尝试删除数据库中的冗余的过程 - 在您的示例中，user_purchase中的名称字段是多余的 - 我可以通过使用i_id在项目表中查找项目的名称来查找该项目的名称。

So if we were to look at normalizing user_purchase we'd probably remove the name field and use a JOIN to retrieve that when we needed it. This would, of course, also mean we don't need the second FOREIGN KEY reference to items.

因此，如果我们要查看规范化user_purchase，我们可能会删除名称字段并使用JOIN在我们需要时检索它。当然，这也意味着我们不需要对项目进行第二次FOREIGN KEY引用。

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 KEY back to items (as it would "break" if the product was re-named).

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