Geek Answers Handbook

Does MySQL need a primary key for many-to-many links table?

Note to Mod: I read about a dozen posts that seemed to address this issue, but none of them answered my question. Do not post this message for deletion; This is not a duplicate question.

I am creating a database for a web gallery that will contain many-to-many relationships. For example, tags and images. Obviously, a third, link, table will be created for this. I see how to use the primary key column in the tag table and image table, but I cannot imagine it for use in the link table. It will just take up space on the server. So, I think that I do not have a primary key column in the link table. Does MySQL provide this? Or, are there good reasons to have a primary key in a link table? Thank.

Link Table:

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

Explanation

Will the primary key in the table split the database?

+5
source share
4 answers

. - . UNIQUE (tag_ids, image_ids)

+6

, .

, . (tag_id, image_id).

, , , , , , . .

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


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

( , "+" - MySQL. . SQL "&", MySQL - .)

, , 25,34 253,4 - , 2534.

? , . :

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

(), :

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'

: , , . , db , image_id.

+8

, / tag_id image_id, .. PRIMARY KEY (tag_id, image_id). .

+5
source

When working with MySQL Workbench, this is very desirable, because without a primary key, it will not allow access to your tables, except for reading, which is a pain when trying to test your database. Although it seems wasteful to have a PC that is never referenced in a relationship.

0
source

More articles:


All Articles