MySQL：一个表中的两个外键引用另一个表 [英] MySQL: Two foreign keys in one table referring to another table

问题描述

我遇到了一些似乎很简单的东西，但是我再次搔搔我的头。我有一张用户表：

  user_id（PK）|用户名|电子邮件| 
  

...以及一个用户查看了另一个用户时的视图表： / p>

  view_id（PK）| viewer_id | visited_id | view_date 
  

viewer_id和visited_id都是user_ids，允许我单独搜索实例一个用户是查看器或正在查看的用户。

我最初认为这两列都是外键，但是在我的schema.yml文件中创建了表（我使用Doctrine 1.2）并指定了两个单独的外部关系（每列一个），似乎Doctrine仅考虑了这两个表之间的第一个列出的外部关系（user_id> viewer_id）。

现在让我感到困惑，这是否是正确的MySQL行为，在Doctrine中的一个问题，或者我正在接近这个问题，或者什么都不用担心！一个表中有两个独立的外键映射到另一个表中的同一列吗？假设一个JOIN仍然可以通过user_id访问视图，这是否合乎逻辑？我有错了吗？

感谢您的时间。

编辑 - 模式文件：

 用户：
关系：
查看：{class：View，local：user_id ，foreign：viewer_id，type：many，foreignType：one，alias：View，foreignAlias：User} 
查看：{class：View，local：user_id，foreign：seen_id，type：many，foreignType：one，alias：查看，foreignAlias：User} 
 
 ...只有区别是viewer_id / seen_id 
  

解决方案

这里我们去：
你为关系指定了相同的别名。

 用户：
关系：
已查看_by：
类：查看
本地：user_id 
外观：已查看
类型：许多
外部类型：一个
 foreignAlias：查看
 
查看：
类：查看
本地：user_id 
外观：viewer_id 
类型：许多
 foreignType：一个
 foreignAlias：viewer 
  

或者您设置了不同的多对多关系：

 用户：
关系：
 visited_by：
类：用户
本地：view_id 
 foreign：viewer_id，
 refClass：查看
查看：
类：用户
本地：viewer_id 
外观：已查看
 refClass：查看
  

和查看应该看起来像

 查看：
列：
 visited_id：
类型：integer 
 primary：true 
 viewer_id：
类型：整数
主要：真
  

关于。

I've come across something that seemed simple before but has me scratching my head again. I have a table for users:

user_id (PK) | username| email | something

... and a table for "views" for when one user has viewed another user:

view_id (PK) | viewer_id | viewed_id | view_date

The "viewer_id" and "viewed_id" are both user_ids, allowing me to search separately for instances when a user was the viewer or the one being viewed.

I initially thought that both of these columns would be foreign keys, but having created the tables in my schema.yml file (I'm using Doctrine 1.2) and specified two separate foreign relationships (one for each column), it seems Doctrine only takes into account the first listed foreign relationship between these two tables (user_id > viewer_id).

It's got me confused now whether this is correct MySQL behaviour, a problem in Doctrine, or a problem in the way I'm approaching this, or nothing to worry about! Can there be two separate foreign keys from one table mapped to the same column in another table? Is it even logical, given that a JOIN would still give me access to "views" through a user_id? Have I got it wrong?

Thanks for your time.

EDIT - The schema file:

User:
relations:
View: {class: View, local: user_id, foreign: viewer_id, type: many, foreignType: one, alias: View, foreignAlias: User}
View: {class: View, local: user_id, foreign: viewed_id, type: many, foreignType: one, alias: View, foreignAlias: User}

... only difference is viewer_id/viewed_id

解决方案

And here we go: You specified the same aliases for the relations.

User:
  relations:
    viewed_by: 
       class: View
       local: user_id
       foreign: viewed_id
       type: many
       foreignType: one
       foreignAlias: viewed

    viewed:
      class: View
      local: user_id
      foreign: viewer_id
      type: many
      foreignType: one
      foreignAlias: viewer

Or you set up the whole many-to-many relation differently:

User:
   relations:
     viewed_by: 
       class: User 
       local: viewed_id
       foreign: viewer_id,
       refClass: View
     viewed:
       class: User
       local:viewer_id
       foreign: viewed_id
       refClass: View

and View should look like

View:
  columns:
    viewed_id:
      type: integer
      primary: true
    viewer_id:
      type: integer
      primary: true

See the Doctrine documentation on many-to-many relationships.

这篇关于MySQL：一个表中的两个外键引用另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！