Bug #14404 AUTO_INCREMENT

• Submitted: 27 Oct 2005 18:23
• Modified: 27 Oct 2005 19:00
• Reporter: Jean-Francois Ramier
• Status: Not a Bug
• Category: MySQL Query Browser
• Severity: S2 (Serious)
• Version: 5.0
• OS: Windows (xp)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[27 Oct 2005 18:23] Jean-Francois Ramier

Description:
Trying to use AUTO_INCREMENT with SQL SERVER conversion of "Identety Increment" return error 1075

How to repeat:
Here is an example taken from MYSQL AUTO_INCREMENT Documention
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)
)
and the error that follow
Incorrect table definition; there can be only one auto column and it must be defined as a key 1075

[27 Oct 2005 19:00] MySQL Verification Team

The sample you showed fails because you have the InnoDB storage engine
as default engine. See the below restriction:

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

"For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the AUTO_INCREMENT column may be part of a multi-column index."

mysql> 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)
    -> )
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql> alter table animals engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>