CODEWITHSUNDEEP
mysqldatabase-designmany-to-many
ficus
ficus

Reputation: 194

MySQL - many to many table - primary keys and duplicates

Let's assume we have two tables: products and orders. As it is a multi-to-many relationship, I've created an extra table: ordersproducts.

As I read from many threads, two primary keys are recommended in this case - table ordersproducts:

order_id (PK), product_id (PK, FK),

However, in this situation there can't be duplicates in the table. Order_id can be duplicated, but product_id has to be unique, and I need a bit more flexibility - order_id should be able to duplicate and so should product_id.

Works correctly after removing the primary keys, leaving only the foreign key at product_id, however - table without primary keys doesn't seem right, does it?

Upvotes: 0

Views: 1206

Answers (1)

Rick James
Rick James

Reputation: 142278

Always have a PRIMARY KEY. It sounds like you need either (not both) of these:

PRIMARY KEY(order_id, product_id)
PRIMARY KEY(product_id, order_id)

These say (because a MySQL PK must be unique) that there may be duplicates of either column, but the pair is never duplicates.

Since you probably want to go both directions (given an order, find all the products and given a product, find all the orders), you need indexes both ways:

PRIMARY KEY(order_id, product_id),
INDEX(product_id, order_id)

Remember, a PK is UNIQUE and is an INDEX.

Here are more tips on virtually any many:many table. That discusses a generic solution to your generic problem.

Upvotes: 2

Related Questions