How to Remove Multiple Primary Keys in MySQL While Keeping One?

Removing Primary Keys in MySQL

Question: How can you remove primary keys from columns in a MySQL table while preserving one primary key?

Scenario:
Consider the following table schema that maps user customers to permissions in a live MySQL database:

mysql> describe user_customer_permission;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| user_customer_id | int(11) | NO   | PRI | NULL    |                |
| permission_id    | int(11) | NO   | PRI | NULL    |                |
+------------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

You wish to remove the primary keys for user_customer_id and permission_id while maintaining id as the primary key. However, attempting to do so with the command alter table user_customer_permission drop primary key; results in the error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Answer:

Autoincrement columns require an index. Without an index, maintaining an autoincrement column becomes too expensive. Therefore, MySQL requires an autoincrement column to be a leftmost part of an index.

To remove the primary key from user_customer_id and permission_id, follow these steps:

1. Remove the autoincrement property from id:

   ALTER TABLE user_customer_permission MODIFY id INT NOT NULL;

2. Drop the primary key:

   ALTER TABLE user_customer_permission DROP PRIMARY KEY;

Note that the composite PRIMARY KEY originally covered all three columns, and id is not guaranteed to be unique. However, if id happens to be unique, you can make it a PRIMARY KEY and AUTO_INCREMENT again:

ALTER TABLE user_customer_permission MODIFY id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

The above is the detailed content of How to Remove Multiple Primary Keys in MySQL While Keeping One?. For more information, please follow other related articles on the PHP Chinese website!