MySQL ALTER column to remove primary key and auto_increment?

You can use ALTER command to remove primary key and auto_increment. The syntax is as follows −

ALTER TABLE yourTableName DROP PRIMARY KEY,change yourColumnName yourColumnName data type;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table removePrimaryKey
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT,
   -> StudentFirstName varchar(20),
   -> StudentLastName varchar(20),
   -> PRIMARY KEY(StudentId)
   -> );
Query OK, 0 rows affected (0.47 sec)

Check the description of table using DESC command. The syntax is as follows −

desc yourTableName;

Check the description of the table ‘removePrimaryKey’ using DESC. The query is as follows −

mysql> desc removePrimaryKey;

The following is the output −

+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| StudentId        | int(11)     | NO   | PRI | NULL    | auto_increment |
| StudentFirstName | varchar(20) | YES  |     | NULL    |                |
| StudentLastName  | varchar(20) | YES  |     | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Now look at the column Key and Extra above. In the Key column, there is PRI and in the Extra column there is auto_increment. You need to remove both of them, using the ALTER DROP command discussed in the beginning −

The query is as follows −

mysql> alter table removePrimaryKey DROP PRIMARY KEY,change StudentId StudentId int;
Query OK, 0 rows affected (1.79 sec)
Records − 0 Duplicates − 0 Warnings − 0

We have successfully removed primary key and auto increment above. Now you check the primary key and auto_increment have been removed from the table or not using the following query −

mysql> desc removePrimaryKey;

The following is the output −

+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| StudentId        | int(11)     | YES  |     | NULL    |       |
| StudentFirstName | varchar(20) | YES  |     | NULL    |       |
| StudentLastName  | varchar(20) | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
3 rows in set (0.07 sec)