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

Without a PRIMARY KEY, maintaining an autoincrement column becomes too expensive, that's whay MySQL requires an autoincrement column to be a leftmost part of the PRIMARY KEY.

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;

Answer 2

ALTER TABLE `user_customer_permission` MODIFY `id` INT;
ALTER TABLE `user_customer_permission` DROP PRIMARY KEY;

Answer 3

I believe Quassnoi has answered your direct question. Just a side note: Maybe this is just some awkward wording on your part, but you seem to be under the impression that you have three primary keys, one on each field. This is not the case. By definition, you can only have one primary key. What you have here is a primary key that is a composite of three fields. Thus, you cannot "drop the primary key on a column". You can drop the primary key, or not drop the primary key. If you want a primary key that only includes one column, you can drop the existing primary key on 3 columns and create a new primary key on 1 column.

Answer 4

One Line:

ALTER TABLE  `user_customer_permission` DROP PRIMARY KEY , ADD PRIMARY KEY (  `id` )

You will also not lose the auto-increment and have to re-add it which could have side-effects.

Answer 5

"if you restore the primary key, you sure may revert it back to AUTO_INCREMENT"

There should be no question of whether or not it is desirable to "restore the PK property" and "restore the autoincrement property" of the ID column.

Given that it WAS an autoincrement in the prior definition of the table, it is quite likely that there exists some program that inserts into this table without providing an ID value (because the ID column is autoincrement anyway).

Any such program's operation will break by not restoring the autoincrement property.