How can we remove a compound PRIMARY KEY constraint that applies to multiple columns of an existing MySQL table?

We can remove compound PRIMARY KEY constraints from multiple columns of an existing table by using DROP keyword and ALTER TABLE statement.

Example

Suppose we have a table "Room_allotment" with composite primary key constraints on "ID" and "RoomNo" columns as follows -

mysql> describe room_allotment;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | 0       |       |
| Name   | varchar(20) | NO   | PRI |         |       |
| RoomNo | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

Now, if we If we want to delete the compound PRIMARY KEY constraint, then we can use the ALTER TABLE statement as shown below -

mysql> Alter table room_allotment DROP PRIMARY KEY;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0  

mysql> describe room_allotment;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| Name   | varchar(20) | NO   |     |         |       |
| RoomNo | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.07 sec)

The above result set shows that the compound PRIMARY KEY constraint of columns "ID" and "RoomNo" has been deleted.

The above is the detailed content of How can we remove a compound PRIMARY KEY constraint that applies to multiple columns of an existing MySQL table?. For more information, please follow other related articles on the PHP Chinese website!