删除主键时,出错:[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

alter table table_name drop primary key; # [Err] 1075

这是因为,建表时,该主键设置的是自增属性:即AUTO_INCREMENT

而自增列只能有1列,且这列必须为key,也就是建表的时候,如果是自增列,必须用primary key标识

例如该表中的 (id) 这一列:

create table if not exists table_name(
   id INT UNSIGNED AUTO_INCREMENT,
   name_people VARCHAR(40) NOT NULL,
   submission_time DATETIME,
   PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

解决方法:

建表时,不要加入自增属性,之后就可以删除主键,例如:

create table if not exists table_name_2(
   id INT UNSIGNED,
   name_people VARCHAR(40) NOT NULL,
   submission_time DATETIME,
   PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后可以删除主键:

alter table table_name_2 drop primary key; 

# 欢迎交流

01-04 03:02