Defining Composite Key with Auto Increment in MySQL

Question

Scenario:

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)

Table:

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) ); 

Of course, there has to be something wrong with my query, because of which the error thrown is:

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

What I am trying to achieve:

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.

i.e. The data in table should look like:

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).

Answer 1

You can't have MySQL do this for you automatically for InnoDB tables - you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key.

Something like the following should work

DELIMITER $$  CREATE TRIGGER xxx BEFORE INSERT ON issue_log FOR EACH ROW BEGIN     SET NEW.sr_no = (        SELECT IFNULL(MAX(sr_no), 0) + 1        FROM issue_log        WHERE app_id  = NEW.app_id          AND test_id = NEW.test_id     ); END $$  DELIMITER ;