title: What is the correct way to increment a field making up part of a composite key
body: <p>I have a bunch of tables whose primary key is made up of the foreign keys of other tables (Composite key).</p>
  
  <p>Therefore the attributes (as a very cut down version) might look like this, please note that I am only showing the attributes that make up the key - there would be and are other attributes that are stored alongside the composite key:</p>
  
  <pre><code>A[aPK] 1:M B[bPK, aFK] 1:M C[cPK, bFK, aFK]
  </code></pre>
  
  <p>as data this could look like:</p>
  
  <pre><code>A[aPK]: 
  1
  2
  
  B[bPK, aFK]:
  1, 1
  2, 1
  1, 2
  2, 2
  
  C[cPK, bFK, aFK]:
  1, 1, 1
  2, 1, 1
  1, 2, 1
  2, 2, 1
  1, 1, 2
  2, 1, 2
  1, 2, 2
  2, 2, 2
  </code></pre>
  
  <p>As you can see each of the rows are unique and although having the left most attribute set as an auto-number would look like this:</p>
  
  <pre><code>C[cPK, bFK, aFK]:
  1, 1, 1
  2, 1, 1
  3, 2, 1
  4, 2, 1
  5, 1, 2
  6, 1, 2
  7, 2, 2
  8, 2, 2
  </code></pre>
  
  <p>It would remove the need for the 2nd, and 3d column to be part of the composite key, which defeats the object and would give me a single auto-incremented primary key.</p>
  
  <p>I've got this running in a MSSQL DBMS and I'm looking for the best way to increment the left most column, in each table when a new tuple is added to it.</p>
  
  <p>I can't use the Auto Increment Identity Specification option as that has no idea that it is part of a composite key.</p>
  
  <p>I also don't (think I) want to use any aggregate function such as: MAX(field)+1 as this will have adverse affects with multiple users inputting data, rolling back etc. There might however be a nice trigger based option here, but I'm not sure.</p>
  
  <p>This must be a common issue so I'm hoping that someone has a lovely solution.</p>
  
  <p>As a side which may or may not affect the answer, I'm using Entity Framework 1.0 as my ORM, within a c# MVC application.</p>