Jump to content


Photo

TRANSACTIONS+AUTO_INCREMENT+multifield PRIMARY KEY


  • Please log in to reply
1 reply to this topic

#1 danny_ebbers

danny_ebbers
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationNetherlands

Posted 08 May 2003 - 02:02 PM

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

#2 barbatruc

barbatruc
  • Members
  • PipPip
  • Member
  • 28 posts
  • LocationMontreal, Quebec, Canada

Posted 08 May 2003 - 04:43 PM

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.
Unfortunately, PHP \'empowered every moron with a copy of Windows notepad to be \"web programmers\". (...) Give PHP a real INFRASTRUCTURE. Use PEAR!!!\'




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users