title: SQL Server unique auto-increment column in the context of another column
body: <p>Suppose the table with two columns:</p>
  
  <pre><code>ParentEntityId int foreign key
  Number int
  </code></pre>
  
  <p><code>ParentEntityId</code> is a foreign key to another table.  </p>
  
  <p><code>Number</code> is a <em>local</em> identity, i.e. it is unique within single <code>ParentEntityId</code>.</p>
  
  <p>Uniqueness is easily achieved via unique key over these two columns.  </p>
  
  <p><strong>How to make <code>Number</code> be automatically incremented in the context of the <code>ParentEntityId</code> on insert?</strong></p>
  
  <hr>
  
  <p><strong>Addendum 1</strong></p>
  
  <p>To clarify the problem, here is an abstract.</p>
  
  <p><code>ParentEntity</code> has multiple <code>ChildEntity</code>, and each <code>ChiildEntity</code> should have an unique incremental <code>Number</code> in the context of its <code>ParentEntity</code>.</p>
  
  <hr>
  
  <p><strong>Addendum 2</strong></p>
  
  <p>Treat <code>ParentEntity</code> as a <strong>Customer</strong>.  </p>
  
  <p>Treat <code>ChildEntity</code> as an <strong>Order</strong>.  </p>
  
  <p>So, orders for every customer should be numbered 1, 2, 3 and so on.</p>