MySQL table design: Primary Foreign Multi-Column Key possible?

I am currently thinking about the database design of a 'friends' table (many-to-many, stores the friendships between users).

Columns: user_id friend_id

Does it make sense to prefer a multi-column primary key instead of an additional 'friendship_id' column?

If so, can I add 开发者_开发知识库Foreign Keys for both columns?

The fastest way for the database to work on this is probably

PRIMARY KEY ('user_id', 'friend_id')

This ensures that they are unique combinations, and at the same time both can be foreign keys. Maybe you want an index on user_id too, so that you can fast look up all friends for one user.

INDEX ('user_id')

There is no need to have a surrogate key, because it creates extra work related to maintaining the table. The combinations are unique anyways, and you already know both the ids in question.

Yes, you can indeed create two foreign key columns, this is often how this association is designed. You can also specify that two columns together are unique so (user_id,friend_id) is unique.

Edit: An example of this may be:

CREAT TABLE friendship (
    user_id INT,
    friend_id INT,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (friend_id) REFERENCES user(id),
    UNIQUE(user_id,friend_id)
);

I'd go for a single-column surrogate key (friendship_id) plus unique constraint and an index on the user_id, friend_id combination. I'd use the surrogate key for consistency, since you seem to be using surrogates (user_id is presumably referring to a column id on user etc.) on other tables.

if you need to track friendship events, you could have a friendship_id (say you want a box listing the most recent friendships in the system with a link to details), but if your data model doesn't require that relationship, a multi-column primary key would be just fine. you can create it like so:

create table friend (
  user_id int,
  friend_id int,
  foreign key (user_id) references user(id),
  foreign key (friend_id) references user(id),
  primary key (user_id, friend_id)
);

there will be an index on both columns individually created by the foreign key constraint, and a multi-column unique index on user_id, friend_id.