Duplicate PK in MySQL

Question

Hi -

I was told a while ago on this site that the only way to go with a many-to-many (in my case a facebook-ish "friend-ing" system) is to do something like this:

uid(PK) friend_id
4       23
4       20
4       54
32      20
32      89

Wont this leave me with lots of identical primary keys (which i believe isn't possible?) And if I can't set uid as a PK, how can I quickly search the table? There must be a way to get away with this with a PK.

Thanks!

Answer 1

If you have a many to many relationship, you can develop a table in between where you create a dual primary key with the UID and the Friend_ID together. That way there should only be one instance of a pair of UID/Friend_ID.

Answer 2

make it a composit key

PK = (uid,frield_id)

Answer 3

Use a composite of both the uid and friend_id as the primary key.

Answer 4

Make them both part of the primary key.

However, you'll also want to make an index on uid (and possibly friend_id) so that search speed doesn't suffer.

Answer 5

You can just make a compound primary key:

create table fbook (uid int, friend_id int, primary key(uid, friend_id));