如何使用外键与PHP [英] How to use foreign keys with PHP

问题描述

所以我明白如何创建外键，我知道什么是FK的目的。但我有一个问题，在理解如何使用它们。我询问了有关外键的问题此处（点击链接）

这是我做的：

  CREATE TABLE用户b 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）
）; 
  

现在我的问题是如何使用PHP最大限度地利用这个？从上面的链接，人们建议在user_purchase表中只使用一个外键是好的，但如果我想要多个列呢？为什么我们不为同一个表的不同列使用多个外键？

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

我还需要理解术语规范化和反规范化。如果你能提供一些链接，以解释这些条款非常详细的例子，或者如果你有一些伟大的书，数据库设计，实施等初学者的建议，我会很感激。

非常感谢。

解决方案

外键列/约束消歧

所以我知道如何创建外键，我知道什么是
的FK的目的。但我有一个理解如何使用
的问题。

假设你引用外键

。

这里有一个长的：

我们习惯于将列作为外键引用到其他表。特别是在标准化过程中，诸如 user_purchase.i_id 之类的短语是 items 表的外键， 会很常见。虽然这是描述关系的完美有效的方法，但是当我们到达实现阶段时，它可能会有点模糊。

假设您已经创建了表 / em> FOREIGN KEY 子句：

  CREATE TABLE user b $ b 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，
） ; 
  

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

 用户user_purchase项目
 | id username | | id i_id | | i_id name price | 
 | 23 john | | 55 10 | | 10巧克力棒3.42 | 
 | 55 mary | | 70 10 | | 33手机82.11 | 
 | 70 fred | | 70 33 | | 54牙膏8.67 | 
 | 55 10 | | 26玩具车6.00 | 
 | 70 26 | 
  

关系存在。它通过 user_purchase 表实现，该表保存有关谁买了什么的信息。如果我们要查询数据库的相关报告，我们会这样做：

  select * from user_purchase p 
 join user u on（p.id = u.id）
加入项目i on（p.i_id = i.i_id）
  

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

现在， ：

  insert into user_purchase（id，i_id）values（23,99）
  

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

这是外键约束的作用。通过在 user_purchase 表定义中指定 FOREIGN KEY（i_id）REFERENCES项目（i_id），我们基本上给予RDBMS a规则： items.i_id 列中未包含的 i_id 值的条目不可接受。换句话说，在外键列实现引用时，外键约束强制引用完整性< 。

请注意，上述选择你定义了一个FK约束。因此， 不使用FK约束，RDBMS会保护您的数据。

冗余

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

问自己：想要吗？如果两个外键用于相同的目的，冗余将最终让你陷入麻烦。考虑以下数据：

  user_purchase items 
 | id i_id name | | i_id name price | 
 | 55 10巧克力棒| | 10巧克力棒3.42 | 
 | 70 10巧克力| | 33手机82.11 | 
 | 70 33手机| | 54牙膏8.67 | 
 | 55 10牙膏| | 26玩具车6.00 | 
 | 70 26玩具车| 
  

这张图片出了什么问题？用户 55 买了两个巧克力棒，还是巧克力棒和牙膏？这种模糊性可能导致大量的努力来保持数据同步，如果我们只保留一个外键，这将是不必要的。事实上，为什么不将 name 列全部删除，因为它是由关系所隐含的。

我们可以通过实现一个复合外键，通过为 items 表设置 PRIMARY KEY（i_id，name） （或定义一个额外的 UNIQUE（i_id，name）索引，这不是真的），然后设置一个 FOREIGN KEY REFERENCES项（i_id，name）。这样， items 表中只存在（i_id，name）夫妇对 user_purchases 有效。除了一个外键的事实，这种方法是完全不必要的，前提是 i_id 列已足以标识项目（对于名称列...不能说相同）。

但是，没有规则对表使用多个外键。事实上，有些情况下需要这样的方法。考虑一个人（id，姓名）表和父母（人，父亲，母亲）数据：

 个人父
 | id name | |人父亲母亲| 
 | 14 John | | 21 14 59 | 
 | 43 Jane | | 14 76 43 | 
 | 21 Mike | 
 | 76 Frank | 
 | 59 Mary |显然， parent 的所有三列，表是 person 的外键。 不同的关系，但是三个不同的：因为一个人的父母也是人，两个对应的列必须引用同一个表 person 。不过请注意，三个栏位不仅可以 而且必须在同一个中引用不同的人 code> parent 行，因为没有人是他自己的父母，父亲的父亲也是他的母亲。
 
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)

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?

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.

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.
解决方案
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.
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.

Suppose you have created your tables without the FOREIGN KEY clauses:
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:
  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:
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. 

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.

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.

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.

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

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

                        
这篇关于如何使用外键与PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！