mysql - Composite key - one a foreign key, the other auto-incrementing -

i'm having trouble creating table in mysql. essentially, need composite key using "client_id" , "problem_id". in table "clients", "client_id" primary key want keep relationship, , "problem_id" needs auto incrementing.

create table problems ( client_id bigint(10) not null references clients(client_id), problem_id int not null auto_increment, status char(1) not null, description varchar(100) not null, start_date varchar(10) not null, end_date varchar(10), primary key (client_id, problem_id) ); 

mysql won't accept this, seems logical me. how can achieve such table?

two problems:

• innodb requires auto_increment column first column in primary key.

• innodb not support column-level references syntax, supports table-level foreign key constraint syntax.

this should work:

create table problems (  client_id bigint(10) not null,  problem_id int not null auto_increment,  status char(1) not null,  description varchar(100) not null,  start_date varchar(10) not null,  end_date varchar(10),  primary key (problem_id, client_id),  foreign key (client_id) references clients(client_id) ); 

however, means clustered index (the primary key) going benefit lookups problem_id, not lookups client_id.