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 <f...@thefsb.org> 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" <yanghates...@gmail.com> >> Sent: Monday, January 25, 2010 10:21am >> To: mysql@lists.mysql.com >> 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=...@thefsb.org >> >> >> >> > > > > -- > 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=paul.dub...@sun.com > -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
