List:General Discussion« Previous MessageNext Message »
From:Wagner Bianchi Date:January 26 2010 3:07pm
Subject:Fwd: auto_increment without primary key in innodb?
View as plain text  
 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.Dubois@stripped>

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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>



-- 
Wagner Bianchi - Web System Developer and Database Administrator
Phone: (31) 8654-9510 / 3272-0226
E-mail: wagnerbianchijr@stripped
Lattes: http://lattes.cnpq.br/2041067758113940
Twitter: http://twitter.com/wagnerbianchi
Skype: infodbacet

Thread
Fwd: auto_increment without primary key in innodb?Wagner Bianchi26 Jan