there can be only one auto column and it must be defined as a key

MySQL主键与非主键设置自增问题：
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

开始：
MySQL [liangcb]> show create table a5; 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | 
Table | Create Table    
                                                                                                                                      | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | a5    | CREATE TABLE `a5` (   `id` int(11) DEFAULT NULL,   `name` char(30) DEFAULT NULL,   `id1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
 MySQL [liangcb]> desc a5;
  +-------+----------+------+-----+---------+-------+ | 
 Field | Type     | Null | Key | Default | Extra |
   +-------+----------+------+-----+---------+-------+ |
 id    | int(11)  | YES  || NULL    || | name  | char(30) | YES  || NULL    || | id1   | int(11)  | YES  || NULL    
 || +-------+----------+------+-----+---------+-------+ 
 3 rows in set (0.00 sec)  
 
 MySQL [liangcb]> alter table a5 CHANGE  id id int auto_increment; 
 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 
 //报错只能有一个自增列，并且必须将其定义为key
 
 MySQL [liangcb]> alter table a5 CHANGE  id id int auto_increment PRIMARY KEY ;
 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 
 //设置主键这里没有问题了
 
 MySQL [liangcb]> desc a5;
 +-------+----------+------+-----+---------+----------------+ | Field | Type     | Null | Key | Default | Extra          
 | +-------+----------+------+-----+---------+----------------+ | 
 id    | int(11)  | NO   | PRI | NULL    | auto_increment | | name  | char(30) | YES  || NULL    
 || | id1   | int(11)  | YES  || NULL    || 
 +-------+----------+------+-----+---------+----------------+ 
 3 rows in set (0.00 sec)
//给非主键添加自增

MySQL [liangcb]> ALTER TABLE a5
-> MODIFY COLUMN id int(11) NOT NULL FIRST ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [liangcb]> desc a5;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    ||
| name  | char(30) | YES  || NULL    ||
| id1   | int(11)  | YES  || NULL    ||
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MySQL [liangcb]> alter table a5 CHANGE id1 id1 int auto_increment;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
//报错必须为一个key

MySQL [liangcb]> alter table a5 CHANGE id1 id1 int auto_increment Unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [liangcb]> desc a5;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    ||
| name  | char(30) | YES  || NULL    ||
| id1   | int(11)  | NO   | UNI | NULL    | auto_increment |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

总结：
1、一个表自增字段只能有一个；如果主键已经是自增了，那么这个表不能再有自增了。
2、ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
添加自增字段必须为一个key无论是Unique还是PRIMARY KEY都可以。