Remove Primary Key in MySQL

Question

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 1

To maintain an autoincrement column without the indexes becomes expensive. Therefore, MySQL needs an autoincrement column to be the leftmost part of the index. 

You must remove the auto-increment property before you are dropping the key:

ALTER TABLE user_customer_permission MODIFY id INT NOT NULL;

ALTER TABLE user_customer_permission DROP PRIMARY KEY;

Note: If you observe here you have the composite PRIMARY KEY which covers all three columns and ids which are not guaranteed to be unique.

But, if it happens to be unique, then you can make it as PRIMARY KEY and AUTO_INCREMENT again like this:

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