title: Composite Primary Keys : Good or Bad?
body: <p>Although one can use composite primary keys, for the case below, is it really a bad practice? The consensus on Stackoveflow <em>seems to go both ways on this issue</em>.</p>
  <p>Why?</p>
  <hr />
  <p>I want to store payments for the orders in a separate table. The reason is that, an order can have many items which are handled in a separate table in the form of many to many relationship. Now, if I don't use composite primary keys for my payment table, I'll lose my unique <code>PaymentID</code>:</p>
  <pre><code>[PaymentId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [OrderId] INT NOT NULL PRIMARY KEY --Also a Foreign Key--
  </code></pre>
  <p>Now, if I just remove the Primary Key for the <code>OrderId</code>, I'll lose my one to one relationship here so <code>Many OrderIds can be associated to many PaymentIds</code>, and I don't want this.</p>
  <p>This seems to be why other answers on SO have concluded (mostly) that the composite key is a bad idea. If it is bad, what's the best practice then?</p>