Yeah, Paul... This is so clear...the auto_increment column may be indexed like:
- KEY(); - UNIQUE(); - PRIMARY KEY() ...when you create or alter a table. -- Wagner Bianchi 2010/1/25 Paul DuBois <paul.dub...@sun.com> 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=wagnerbianch...@gmail.com > > -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
