MySQL items of an order without primary key?

i have two tables in my database which belongs to each other.
mp_order and mp_order_items.

mp_order has the main informations of an order of a customer like adress, date etc.
(order_id, customer_company, customer_name, customer_adress, order_date, … [etc.])

mp_order_items has the priducts/items which was ordered
(order_id, item_id, item_qty)

Due to order_id and item_id can repeat (but not in combination) i cant set one column as primary key.
Should i implement another column as unique identifier for the single entries or is it valid to have a table without primary key?

Answer

You have two options:

  • Define a primary key on (order_id, item_id)
  • Define a synthetic primary key, such as an auto-incremented column.

I prefer the second method. It is more flexible for the future:

  • Perhaps an order could contain the same items, but with different pricing or shipping addresses or shipping times.
  • The rows are uniquely defined with a single number, which makes it easier to find them if you need to modify rows in the future.
  • The rows are more easily referenced in another table, for instance, if you had a returns table or if the items.

Of course, having a composite primary key also works and is a very viable method for implementing the logic as well.