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
>
