[TRANSACTIONS+AUTO_INCREMENT+multifield PRIMARY KEY]

I would like to create this table in InnoDB because i need transactions

CREATE TABLE dn_jobs ( 

  jobnr_week int(2) NOT NULL, 

  jobnr_volgnr int(4) NOT NULL AUTO_INCREMENT, 

  jobnr_jaar int(4) NOT NULL, 

  i_ordernr int(8), 

  v_ordernr int(8), 

  levnr int(6), 

  leersoort varchar(10), 

  status char(3), 

  administratie char(4), 

  ht_jobnr varchar(30), 

  PRIMARY KEY(jobnr_week,jobnr_volgnr,jobnr_jaar) 

  ) 

 

when i add

 TYPE = MyIsam

then it works fine

but when i use

 TYPE = InnoDB 

then it gives the following error:

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

 

The point is that i have to insert records when knowing all weeknr and year

 

The auto increment should depend on the rest of the primary key, thats why i cannot use UNIQUE INDICES instead of Primary Key?

 

so for the primary key i could insert records like this

12 - 0 - 2003

12 - 1 - 2003

12 - 2 - 2003

13 - 0 - 2003

13 - 1 - 2003

13 - 2 - 2003

 

Anyone any suggestion how to implement this successfull in my application