今天在创建有联合主建的表时，出现下面的错误：
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