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=gcdmg-mysql-2@m.gmane.org

RE: auto_increment without primary key in innodb?

it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/creat=
e-table.html "Note=0A"There can be only one AUTO_INCREMENT column per =
table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCR=
EMENT column works properly only if it contains only positive values. Inser=
ting a negative number is regarded as inserting a very large positive numbe=
r. This is done to avoid precision problems when numbers â€=9Cwrap=E2=
€ over from positive to negative and also to ensure that you do not ac=
cidentally get an AUTO_INCREMENT column that contains 0." -----Origina=
l Message-----=0AFrom: "Yang Zhang" =0ASent: Monday=
, January 25, 2010 10:21am=0ATo: mysql@lists.mysql.com=0ASubject: auto_incr=
ement without primary key in innodb? In innodb, is it possible to have=
an auto_increment field without=0Amaking it a (part of a) primary key? Why=
is this a requirement? I'm=0Agetting the following error. Thanks in advanc=
e. ERROR 1075 (42000): Incorrect table definition; there can be only o=
ne=0Aauto column and it must be defined as a key=0A-- =0AYang Zhang=0Ahttp:=
//www.mit.edu/~y_z/ -- =0AMySQL General Mailing List=0AFor list archiv=
es: http://lists.mysql.com/mysql=0ATo unsubscribe: http://lists.mysql.co=
m/mysql?unsub=3Dfsb@thefsb.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: auto_increment without primary key in innodb?

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 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 indexe=
d, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works prope=
rly only if it contains only positive values. Inserting a negative number i=
s regarded as inserting a very large positive number. This is done to avoid=
precision problems when numbers =93wrap=94 over from positive to negative =
and also to ensure that you do not accidentally get an AUTO_INCREMENT colum=
n that contains 0."
>
> -----Original Message-----
> From: "Yang Zhang"
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dfsb@thefsb.or=
g
>
>
>
>



--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: auto_increment without primary key in innodb?

2010/1/25 Yang Zhang :
> 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.

Non-primary key works for me, as documented:

---------------------------------->8------------------------ --------
mysql> create table test_ai (i int PRIMARY KEY, c int auto_increment, index(c));
Query OK, 0 rows affected (0,07 sec)

mysql> desc test_ai;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| i | int(11) | NO | PRI | NULL | |
| c | int(11) | NO | MUL | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0,00 sec)

mysql> insert into test_ai (i) values (100), (200);
Query OK, 2 rows affected (0,00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test_ai;
+-----+---+
| i | c |
+-----+---+
| 100 | 1 |
| 200 | 2 |
+-----+---+
2 rows in set (0,00 sec)
---------------------------------->8------------------------ --------

Regards,
--
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: auto_increment without primary key in innodb?

The requirement is that it be indexed. The index need not be a primar=
y key.

mysql> create table t (i int not null auto_increment, index(i)) engin=
e 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 t=
he
> only way I've successfully created a table with auto_increment is b=
y
> making it a primary key. And I still don't understand why this
> requirement is there in the first place.
>=20
> On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster wrot=
e:
>> it's not an innodb thing:
>>=20
>> http://dev.mysql.com/doc/refman/5.0/en/create-table.html
>>=20
>> "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 colum=
n works properly only if it contains only positive values. Inserting =
a negative number is regarded as inserting a very large positive numb=
er. This is done to avoid precision problems when numbers =93wrap=
=94 over from positive to negative and also to ensure that you do not=
accidentally get an AUTO_INCREMENT column that contains 0."
>>=20
>> -----Original Message-----
>> From: "Yang Zhang"
>> Sent: Monday, January 25, 2010 10:21am
>> To: mysql@lists.mysql.com
>> Subject: auto_increment without primary key in innodb?
>>=20
>> 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.
>>=20
>> 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/
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dfsb@thefsb=
..org
>>=20
>>=20
>>=20
>>=20
>=20
>=20
>=20
> --=20
> Yang Zhang
> http://www.mit.edu/~y_z/
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dpaul.dubois=
@sun.com
>=20

--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: auto_increment without primary key in innodb?

yah, mysql only allows one auto increment field n that's used as the
primary key in tables. I don't think it has to be the primary key as
long as it is a unique key i think that's okay.

so u should be able to do : create table (myid int unsigned not null
auto_increment....., unique key (myid));

but this is effectively a primary key....

if u want some auto incrementing behavior but have it do so only on
certain scenarios and possibly hold null values, you can write an insert
trigger that would update the field on every insert.

Yong.

On Mon, 2010-01-25 at 10:21 -0500, Yang Zhang wrote:
> 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=gcdmg-mysql-2@m.gmane.org

Re: auto_increment without primary key in innodb?

--0016e64ea9226a4d8c047e0f3fcb
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Jan 25, 2010 at 10:08 PM, Yong Lee wrote:

> yah, mysql only allows one auto increment field n that's used as the
> primary key in tables. I don't think it has to be the primary key as
> long as it is a unique key i think that's okay.
>
> so u should be able to do : create table (myid int unsigned not null
> auto_increment....., unique key (myid));
>
> but this is effectively a primary key....
>

Only mostly true :-)

It *is* the same for MyISAM, but for InnoDB the primary key is special, as
that is the one that stores the data inline (clustered index). Additional
unique keys will only contain a reference to the primary key value for the
record.




--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e64ea9226a4d8c047e0f3fcb--

Fwd: auto_increment without primary key in innodb?

--0016e6d99d05a3c251047e12a573
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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

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 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 wor=
ks
> properly only if it contains only positive values. Inserting a negative
> number is regarded as inserting a very large positive number. This is don=
e
> to avoid precision problems when numbers =93wrap=94 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"
> >> 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=3Dfsb@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=3Dpaul.dubois@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=3Dwagnerbianchijr@gmail.c om
>
>



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

--0016e6d99d05a3c251047e12a573--