Does MySQL require a primary key for a many-to-many link table?

Question

Note to Mod: I read through about a dozen posts that seemed to pertain to this issue, but none of them answered my question. Please do not flag this post for deletion; this is not a duplicate question.

I am building a database for a web-gallery that will contain many-to-many relationships. For example, tags and images. Obviously, to accomplish this a third, link, table will be created. I can see a use for having a primary key column in the tags table and the images table, but I can't imagine a use for it in the links table. It would just take up server space. So, I'm thinking of just not having a primary key column in the links table. Does MySQL allow this? Or, would there be any compelling reason to have a primary key in the links table? Thanks.

Link Table:

+--------------+---------+-----------+
| primary key? | tag ids | image ids |
+--------------+---------+-----------+

Clarification

Will not having a primary key in a table break the database?

Answer 1

There is no requirement that you have a primary key.

However, there is also no requirement that a primary key be only one field. In this case you might declare your primary key to be (tag_id, image_id).

You've got a question in reply to another post that gives me the idea that maybe you're thinking you should concatenate the two fields to make the primary key. Don't. Define the key as

alter table link add primary key (tag_id, image_id);

Do NOT say

alter table link add primary key (tag_id + image_id);

(I think "+" is the concatenation operator in MySQL. It's been a while. The SQL standard is "&" but MySQL uses that for something else.)

There's a big difference between the two, namely, in the first case, 25,34 and 253,4 are two different values, while in the second case they both get turned into 2534.

Will you always go from tag to image, or will you also want to go from image to tag? If you need to go in both directions, then you should create two indexes, or a primary key and an index, with the fields in both directions. Like:

create index link_tag_image on link(tag_id, image_id);
create index link_image_tag on link(image_id, tag_id);

If you make only the first (for example), then consider this query:

select tag.name
from image
join link on image.image_id=link.imagae_id
join tag on tag.tag_id=link.tag_id
where image.foo='bar'

This seems plausbile enough: find all the tags that match images that meet a certain condition. But without the second index, this query could take a very long time, because the db will have to read the entire link table sequentially to find all the records with a given image_id.

Answer 2

There is no need for primary key in the link table. Although a compound key is a good idea. Uniqueness can be achieved by using UNIQUE ( tag_ids, image_ids)

Answer 3

Yes, your primary key should be a compound/composite key of tag_id and image_id, i.e. PRIMARY KEY (tag_id, image_id). There's no need for an extra autoincrement column in this case.