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

Question

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?

Answer 1

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)
);

Answer 2

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.

Answer 3

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.

Answer 4

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.