Jump to content

Archived

This topic is now archived and is closed to further replies.

danny_ebbers

TRANSACTIONS+AUTO_INCREMENT+multifield PRIMARY KEY

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.