innodb - Defining Composite Key with Auto Increment in MySQL -

scenario:

i have table references 2 foreign keys, , each unique combination of these foreign keys, has own auto_increment column. need implement composite key identify row unique using combination of these 3 (one foreign keys , 1 auto_increment column, , 1 other column 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 wrong query, because of error thrown is:

error 1075: incorrect table definition; there can 1 auto column , must defined key

what trying achieve:

i have application table (with app_id primary key), each application has set of issues resolved, , each application has multiple number of tests (so test_id col) sr_no col should increment unique app_id , test_id.

i.e. data in table should like:

the database engine innodb. want achieve simplicity possible (i.e. avoid triggers/procedures if possible - suggested similar cases on other questions).

you can't have mysql automatically innodb tables - need use trigger or procedure, or user db engine such myisam. auto incrementing can done single primary key.

something following should work

delimiter $$  create trigger xxx before insert on issue_log each row begin     set new.sr_no = (        select ifnull(max(sr_no), 0) + 1        issue_log        app_id  = new.app_id          , test_id = new.test_id     ); end $$  delimiter ;