如何使用外键与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> parentSo 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 theitems
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 theitems
table (i_id
) -- let's put thename
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 withi_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 theuser_purchase
table definition, we essentially give the RDBMS a rule to follow: entries withi_id
values that are not contained in theitems.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 thename
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 theitems
table (or defining an extraUNIQUE(i_id,name)
index, it doesn't realy matter) and then setting aFOREIGN KEY(i_id,name) REFERENCES items(i_id,name)
. This way, only (i_id,name) couples that exist in theitems
table would be valid foruser_purchases
. Apart from the fact that you would still have one foreign key, this approach is totally unnecessary, provided that thei_id
column is already enough to identify an item (can't say the same for thename
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 aparent(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 toperson
. 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 tableperson
does. Note, however, that the three fields not only can but also have to refer differentperson
s in the sameparent
row, since noone is his own parent and noone's father is his mother as well.这篇关于如何使用外键与PHP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!