Multiple Column Keys with Auto Increment

[DL Neil]

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 <mysql-thr...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[DL Neil]

[BD]

At 01:02 AM 3/13/2002, you wrote:
>Greetings!!!

>
>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?

Edgar,
Not officially. The InnoDb manual states auto-increment fields
must be in a key by themselves. :-(

But I've discovered you can have a compound index with an
auto-increment field as long as the auto-increment field is the *first*
field of the index. So if you switch your primary key to

primary key (TRANS_LOG_ID,TRANS_CODE)

it should work. :-)

Brent

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

[ImpactNET - Edgar R Gutierrez]

Hi BD! :)

First of all, Thanks for the reply....

Anyway, actually what i would like to do is group data so that each group
will have its own "increment" series when data is inserted for that group.

For example

SESSION_ID TRANSACTION LOG_ID

0A0AA 0B0B 1
0A0AA 0B0B 2
0A0AA 0AEA 1
0A0AA 0AEA 2
0A0AA 0AEA 3
0AB0B 0B0B 1

This works if my table is MyISAM. It doesn't work in InnoDB.......

I hope there is a solution...

Thanks again!