title: mysql two column primary key with auto-increment
body: <p>I have multiple databases with the same structure in which data is sometimes copied across. In order to maintain data integrity I am using two columns as the primary key. One is a database id, which links to a table with info about each database. The other is a table key. It is not unique because it may have multiple rows with this value being the same, but different values in the database_id column. </p>
  
  <p>I am planning on making the two columns into a joint primary key. However I also want to set the table key to auto increment - but based on the database_id column.</p>
  
  <p>EG, With this data:</p>
  
  <pre><code>table_id   database_id     other_columns
  1          1
  2          1
  3          1
  1          2
  2          2
  </code></pre>
  
  <p>If I am adding data that includes the dabase_id of 1 then I want table_id to be automatically set to 4. If the dabase_id is entered as 2 then I want table_id to be automatically set to 3. etc.</p>
  
  <p>What is the best way of achieving this in MySql.</p>