is it possible to not give a primary key to an association table?

Hi I’m using phpmyadmin to create a database to control order made from a companyA to other companies. I created two kind of object that the company can order and that needs to be distinguished:

  • Accessories (it can be batteries made by the enterprise using there InventoryItems, or bought from another enterprise)
  • InventoryItems (it can be screws or nails bought from another enterprise)

MCD example

so here’s what I got in my deliver table

phpmydaminview

CREATE TABLE `deliver` (
  `AccesoryId` int(36) NOT NULL,
  `ItemId` int(36) NOT NULL,
  `OrderId` int(36) NOT NULL,
  `quantity` int(11) NOT NULL
)

everything looks great but here are my issues:

  • I can’t make an order that doesn’t contain only accessories or only items.they can’t be NULL because they are part of the primarykey so I tried to remove them from the primary key and only using the orderId but now:
  • I can’t link different Items or Accessories to one order.because it would duplicate the primary key

so I tried to remove the composite primary key to get rid of those issues and to create a relation that would link the primary keys of each table (this would help me be able to link the objects to the order and at the same time let me make them NULL)

now my question is, is it that bad that an association table don’t have any primary key or is there a better way to do this ?

Answer

A table doesn’t have to have a primary key. So generally it’s okay for the deliver table not to have one. What it needs, though, to guarantee consistency:

  • Foreign key constraints of course.
  • A check constraint that in every row exactly one of accessory_id and item_id is null and the other not null.
  • A unique index on (order_id, accessory_id)
  • A unique index on (order_id, item_id)

I’d probably use two deliver tables instead:

  • order_item (order_id, item_id, quantity)
  • order_accessory (order_id, accessory_id, quantity)

Then both would have a primary key (because item_id resp. accessory_id is not null) and its easier to query the data.