- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)