markb markb - 3 months ago 9
SQL Question

Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on 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)


I would like to remove the primary keys for user_customer_id and permission_id and retain the primary key for id.

When I run the command:

alter table user_customer_permission drop primary key;


I get the following error:

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


How can I drop a column's primary key?

Answer

Without an index, maintaining an autoincrement column becomes too expensive, that's why MySQL requires an autoincrement column to be a leftmost part of an index.

You should remove the autoincrement property before dropping the key:

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

Note that you have a composite PRIMARY KEY which covers all three columns and id is not guaranteed to be unique.

If it happens to be unique, you can make it to be a PRIMARY KEY and AUTO_INCREMENT again:

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