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都可以。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。