管理外键

本文介绍了管理外键的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

所以我有一个包含几个表的数据库。

So I have a database with a few tables.

第一个表包含用户ID，名字和姓氏。

The first table contains the user ID, first name and last name.

第二个表包含用户ID，兴趣ID和利率。

The second table contains the user ID, interest ID, and interest rating.

还有另一个具有所有兴趣ID的表。

There is another table that has all of the interest ID's.

对于每个兴趣ID（即使添加了新的兴趣ID），我也需要确保每个用户都有该兴趣ID的条目（即使其空白，或者具有

For every interest ID (even when new ones are added), I need to make sure that each user has an entry for that interest ID (even if its blank, or has defaults).

外键在这种情况下会有所帮助吗？还是在添加新密钥时需要使用PHP更新每条记录？

Will foreign keys help with this scenario? or will I need to use PHP to update each and every record when I add a new key?

推荐答案

对于每个利息ID（即使添加了新的），我也需要确保每个用户都有该利息ID的条目（即使其空白，或者具有默认值）。

For every interest ID (even when new ones are added), I need to make sure that each user has an entry for that interest ID (even if its blank, or has defaults).

听起来您需要外部联接（在其中一个查询中为 LEFT 或 RIGHT ）。

It sounds like you need an OUTER JOIN (either LEFT or RIGHT) in one of your queries instead.

例如，如果您想获得特定人对每种兴趣的兴趣水平：

For example, if you wanted to get the level of interest a particular person has for each interest:

假设您的表格如下所示： 用户： user_id PK 用户

Assuming your tables look like this: users: user_id PK user

user_interests： user_id PK FK interest_id PK FK interest_level

user_interests: user_id PK FK interest_id PK FK interest_level

兴趣： interest_id PK 兴趣

interests: interest_id PK interest

SELECT i.interest, ui.interest_level FROM interests i INNER JOIN user_interests ui USING (interest_id) LEFT JOIN users u USING (user_id) WHERE user_id = ?

？是占位符。

请注意，对于没有数据的兴趣， ui.interest_level 将为空。

Note that ui.interest_level will be null for interests with no data.