管理外键 [英] Managing Foreign Keys

问题描述

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

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.

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