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