The requirement is that it be indexed. The index need not be a primary key.
mysql> create table t (i int not null auto_increment, index(i)) engine innodb;
Query OK, 0 rows affected (0.45 sec)
On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote:
> Right, I saw the docs. I'm fine with creating an index on it, but the
> only way I've successfully created a table with auto_increment is by
> making it a primary key. And I still don't understand why this
> requirement is there in the first place.
>
> On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster <fsb@stripped> wrote:
>> it's not an innodb thing:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/create-table.html
>>
>> "Note
>> "There can be only one AUTO_INCREMENT column per table, it must be indexed, and
> it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it
> contains only positive values. Inserting a negative number is regarded as inserting a very
> large positive number. This is done to avoid precision problems when numbers
> “wrap” over from positive to negative and also to ensure that you do not
> accidentally get an AUTO_INCREMENT column that contains 0."
>>
>> -----Original Message-----
>> From: "Yang Zhang" <yanghatespam@stripped>
>> Sent: Monday, January 25, 2010 10:21am
>> To: mysql@stripped
>> Subject: auto_increment without primary key in innodb?
>>
>> In innodb, is it possible to have an auto_increment field without
>> making it a (part of a) primary key? Why is this a requirement? I'm
>> getting the following error. Thanks in advance.
>>
>> ERROR 1075 (42000): Incorrect table definition; there can be only one
>> auto column and it must be defined as a key
>> --
>> Yang Zhang
>> http://www.mit.edu/~y_z/
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>>
>
>
>
> --
> Yang Zhang
> http://www.mit.edu/~y_z/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
--
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
