mysql 外键引用_MySQL：一个表中的两个外键引用另一个表

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.