How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?

MySQLMySQLi Database

We can remove PRIMARY KEY constraint from a column of an existing table by using DROP keyword along with ALTER TABLE statement.

Example

Suppose we have a table ‘Player’ having a PRIMARY KEY constraint on column ‘ID’ as follows −

mysql> DESCRIBE Player;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    |  int(11)    | NO   | PRI | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

2 rows in set (0.04 sec) 

Now if we want to remove the PRIMARY KEY constraint then we can use ALTER TABLE statement as follows −

mysql> alter table Player DROP PRIMARY KEY;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE Player;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    |  int(11)  | NO     |     | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

2 rows in set (0.04 sec) 

The above result set shows that PRIMARY KEY constraint on column ‘ID’ has been removed.

raja
Published on 16-Feb-2018 19:21:12
Advertisements