Greetings Edgar, > I would just like to know if the following is normal: > > I created a file TRANSACTION_LOG.sql to create an InnoDB table with MULTIPLE > KEYs with one AUTO COLUMN. > > The file consists of the following SQL commands: > > drop table TRANSACTION_LOG; > create table TRANSACTION_LOG (SESSION_ID int(10) not null, TRANS_CODE int(5) > not null, TRANS_LOG_ID int(10) not null auto_increment, primary key > (TRANS_CODE,TRANS_LOG_ID)) TYPE = InnoDB; > > Unfortunately , I get the following error: > > > [root@oracle /root]# mysql accounting < TRANSACTION_LOG.sql > ERROR 1075 at line 2: Incorrect table definition; There can only be one auto > column and it must be defined as a key > > Does InnoDB tables support MULTIPLE KEYS with an AUTO COlumn?
I was intrigued by your question and took a look through the manual. I didn't find anything sufficiently decisive, but: Manual: 3.5.9 Using AUTO_INCREMENT For MyISAM and BDB tables you can specify AUTO_INCREMENT on secondary column in a multi-column key. In this case the generated value for the autoincrement column is calculated as MAX(auto_increment_column)+1) WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. Manual 6.5.3 CREATE TABLE Syntax says pretty much the same thing. I didn't follow through to the InnoDB site/docs - but you've already done that - right!? Whilst the above doesn't cover the InnoDB situation you describe, the inclusive language leads me to expect that one can define an AUTO-INCREMENT as a non-first field in a multi-column (primary) key under MyISAM and DBD, but not under InnoDB. What are you wanting to achieve? If you want a unique id for each row of the table, then AUTO_INCREMENT is the tool but only the ID column is needed to construct a unique key/primary index (by definition) - and the problem goes away. If however you are using AUTO_INCREMENT to differentiate between to identical TRANS_CODE values, then I understand why you have coded the way you have, but the above conclusion seems to apply. Please let us know how you get on, =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
