title: Updating a composite primary key
body: <p>I am struggling with the philosophical discussions about whether or not to use composite primary keys on my SQL Server database. I have always used the surrogate keys in the past and I am challenging myself by leaving my comfort zone to try something different. I have read many discussion but can't come to any kind of solution yet. The struggle I am having is when I have to update a record with the composite PK. </p>
  
  <p>For example, the record in questions is like this: </p>
  
  <pre><code>ContactID, RoleID, EffectiveDate, TerminationDT
  </code></pre>
  
  <p>The PK in this case is the <code>(ContactID, RoleID, EffectiveDate)</code>. <code>TerminationDT</code> can be null. </p>
  
  <p>If in my UI, the user changes the <code>RoleID</code> and then I need to update the record. Using the surrogate key I can do an <code>Update Table Set RoleID = 1 WHERE surrogateID = Z</code>. However, using the Composite Key way, once one of the fields in the composite key changes I have no way to reference the old record to update it without now maintaining somewhere in the UI a reference to the old values. </p>
  
  <p>I do not bind datasources in my UI. I open a connection, get the data and store it in a bucket, then close the connection. What are everyone's opinions? Thanks.</p>