Choice of primary key in a SQL Master Detail scenario [closed]

Question

In a Sales Order Header/ Detail scenario, should the details table have an arbitrary primary key (identity/guid) or should it be made up of the SalesOrderHeaderID and ProductID columns?

This is a general question for a Master/ Detail scenario where it's possible to create a composite unique key in the detail table with 2 or 3 rows. (In the above example we assume that an order cannot contain duplicate products).

Answer 1

If you choose a natural composite key, and your design later changes so that your Order Details becomes the primary key for another table (for example, shipments), then you have to propogate that composite key to the new table. For that reason alone, I would choose an arbitrary identity as the primary key.

Also, this allows for the future situation where you can contain duplicate products on an order

(The answer to this question is of course an ecumenical matter with no correct answer)