title: Defining Composite Key with Auto Increment in MySQL
body: <p><strong>Scenario:</strong></p>
  
  <p>I have a table which references two foreign keys, and for each unique combination of these foreign keys, has its own auto_increment column. I need to implement a Composite Key that will help identify the row as unique using combination of these three (one foreign keys and one auto_increment column, and one other column with non-unique values)</p>
  
  <p><strong>Table:</strong></p>
  
  <pre><code>CREATE  TABLE `issue_log` (
  `sr_no` INT NOT NULL AUTO_INCREMENT ,
    `app_id` INT NOT NULL ,
    `test_id` INT NOT NULL ,
    `issue_name` VARCHAR(255) NOT NULL ,
  primary key (app_id, test_id,sr_no)
  );
  </code></pre>
  
  <p>Of course, there has to be something wrong with my query, because of which the error thrown is:</p>
  
  <blockquote>
    <p>ERROR 1075: Incorrect table definition; there can be only one auto
    column and it must be defined as a key</p>
  </blockquote>
  
  <p><strong>What I am trying to achieve:</strong></p>
  
  <p>I have an Application Table (with app_id as its primary key), each Application has a set of Issues to be resolved, and each Application has multiple number of tests (so the test_id col)
  The sr_no col should increment for unique app_id and test_id.</p>
  
  <p>i.e. The data in table should look like:</p>
  
  <p><img src="https://i.stack.imgur.com/o07nC.png" alt="enter image description here"></p>
  
  <p>The database engine is InnoDB.
  I want to achieve this with as much simplicity as possible (i.e. avoid triggers/procedures if possible - which was suggested for similar cases on other Questions).</p>