mysql: [Err] 1075 – Incorrect table definition; there can be only one auto column and it must be d…

Error when deleting primary key: [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

This is because, when creating a table, the primary key is set with an auto increment attribute: Auto_ INCREMENT

The autoincrement column can only have one column, and this column must be a key, that is, when creating a table, if it is a autoincrement column, it must be identified with a primary key

For example, the (ID) column in the table:

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;

Solution:

When creating a table, do not add the auto increment attribute, and then you can delete the primary key. For example:

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;

You can then delete the primary key:

alter table table_name_2 drop primary key;

Similar Posts: