创建主键时报错：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it mus

今天在创建有联合主建的表时，出现下面的错误：

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

 

mysql> CREATE TABLE animals1 (

    ->     grp ENUM('fish','mammal','bird') NOT NULL,

    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,

    ->     name CHAR(30) NOT NULL,

    ->     PRIMARY KEY (grp,id)

    -> ) ENGINE=innodb;

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

 

--使用MyISAM存储引擎时不会出现问题

mysql> CREATE TABLE animals1 (

    ->     grp ENUM('fish','mammal','bird') NOT NULL,

    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,

    ->     name CHAR(30) NOT NULL,

    ->     PRIMARY KEY (grp,id)

    -> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.00 sec)

 

--创建单字段主键时不会出现问题

mysql> CREATE TABLE animals (

    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,

    ->      name CHAR(30) NOT NULL,

    ->      PRIMARY KEY (id)

    ->  );

Query OK, 0 rows affected (0.00 sec)

 

--查看创建语句

mysql> show create table animals\G;

*************************** 1. row ***************************

       Table: animals

Create Table: CREATE TABLE `animals` (

  `id` mediumint(9) NOT NULL AUTO_INCREMENT,

  `name` char(30) NOT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

解决办法：

把联合主键中的自增列放在联合主建的最左端，创建成功

mysql> drop table animals1;

mysql> CREATE TABLE animals1 (

    ->     grp ENUM('fish','mammal','bird') NOT NULL,

    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,

    ->     name CHAR(30) NOT NULL,

    ->     PRIMARY KEY (id,grp)

    -> );

Query OK, 0 rows affected (0.00 sec)

 

mysql> show create table animals1\G;

*************************** 1. row ***************************

       Table: animals1

Create Table: CREATE TABLE `animals1` (

  `grp` enum('fish','mammal','bird') NOT NULL,

  `id` mediumint(9) NOT NULL AUTO_INCREMENT,

  `name` char(30) NOT NULL,

  PRIMARY KEY(`id`,`grp`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified