From: Ben Clewett
Date: April 22 2008 7:17am
Subject: Re: auto_increment
List-Archive: http://lists.mysql.com/mysql/212429
Message-Id: <480D9100.3080200@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Are you sure, I just get:

CREATE TABLE ...

ERROR 1075 (42000): Incorrect table definition; there can be only one 
auto column and it must be defined as a key

On version 5.0.41.  What version are you using?



Hiep Nguyen wrote:
> hi list,
> 
> reading manual on mysql regarding auto_increment with multiple-column 
> index:
> 
> CREATE TABLE animals (
>     grp ENUM('fish','mammal','bird') NOT NULL,
>     id MEDIUMINT NOT NULL AUTO_INCREMENT,
>     name CHAR(30) NOT NULL,
>     PRIMARY KEY (grp,id)
> );
> 
> INSERT INTO animals (grp,name) VALUES
>     ('mammal','dog'),('mammal','cat'),
>     ('bird','penguin'),('fish','lax'),('mammal','whale'),
>     ('bird','ostrich');
> 
> SELECT * FROM animals ORDER BY grp,id;
> 
> +--------+----+---------+
> | grp    | id | name    |
> +--------+----+---------+
> | fish   |  1 | lax     |
> | mammal |  1 | dog     |
> | mammal |  2 | cat     |
> | mammal |  3 | whale   |
> | bird   |  1 | penguin |
> | bird   |  2 | ostrich |
> +--------+----+---------+
> 
> my question is what id would be if i:
> 
> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND 
> `name`='ostrich' LIMIT 1;
> 
> i'm confused on auto_increment now.
> 
> thanks
>