TRANSACTIONS+AUTO_INCREMENT+multifield PRIMARY KEY

[danny_ebbers]

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

[barbatruc]

Auto_increment fields (mostly int unsigned ones) must be

1) Unique

2) Set as the only one field for the primary key

 

In other words:

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_volgnr),

  )

may do the trick... (not sure but this is not a good idea to set your auto_increment field jobnr_volgnr since auto_increment fields are ALWAYS unique). Can I suggest something else ?

CREATE TABLE dn_jobs ( 

  id int(10) unsigned NOT NULL AUTO_INCREMENT, 

  jobnr_week int(2) NOT NULL, 

  jobnr_volgnr int(4) NOT NULL, 

  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(id),

  UNIQUE(jobnr_week, jobnr_volgnr, jobnr_jaar)

  )

may be alright!

 

Hope this helps!

 

JP.