title: MySQL second auto increment field based on foreign key
body: <p>I've come across this problem numerous times but haven't found a "MySQL way" to solve the issue as such - I have a database that contains <strong>users</strong> and <strong>reports</strong>. Each <strong>report</strong> has an <strong>id</strong> which I display as a <strong>report number</strong> to my users.</p>
  
  <p>The main complaint is that users are confused as to why reports have gone missing from their system. This is not actually the case. It is actually that they are recognizing a gap between their IDs and assume that these are missing reports, when in actual fact, it is simply becasue another user has filled in this auto-incrementing gap.</p>
  
  <p>I need to know if there is a way to do this in MySQL:</p>
  
  <p>Is it possible that I can have a <strong>second</strong> auto-increment field called <strong>report_number</strong> which is <strong>based on a user_id</strong> field which has a different set of auto-increments per user?</p>
  
  <p>e.g.</p>
  
  <pre><code>|------|---------|---------------|
  |  id  | user_id | report_number |
  |------|---------|---------------|
  |  1   |    1    |       1       |
  |  2   |    1    |       2       |
  |  3   |    1    |       3       |
  |  4   |    2    |       1       |
  |  5   |    1    |       4       |
  |  6   |    1    |       5       |
  |  7   |    2    |       2       |
  |  8   |    3    |       1       |
  |  9   |    3    |       2       |
  |------|---------|---------------|
  </code></pre>
  
  <p>I am using <strong>InnoDB</strong> for this as it is quite heavily weighted with foreign-keys. It appears to complain when I add a second auto increment field, but I wasn't sure if there was a different way to do this?</p>