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?

Misgive

For future readers, this is a bad schema for a map table. Having a separate auto-incrementing ID column is at best a hindrance, and encourages breaking referential integrity. The primary key should be on both foreign key columns only. To add a mapping, you INSERT IGNORE, to remove a mapping, DELETE the row matching both FKs. This way, you cannot have duplicates because the primary key enforces correctness. Bonus points if you have proper foreign key relations set up that cascade updates and deletes from the parent tables. – Tantalus

Answer 1

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;

Neese

Wouldn't I then have to restore the id column as auto_increment primary key? alter table user_customer_permission add primary key (id); alter table user_customer_permission change id id int(11) auto_increment; – Misgive

@markb: if you restore the primary key, you sure may revert it back to AUTO_INCREMENT. – Neese

what does this mean autoincrement column to be a leftmost part of the PRIMARY KEY. ? – Terribly

@ArupRakshit: dev.mysql.com/doc/refman/5.6/en/… To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column ai_col must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index. – Neese

it says PRIMARY is not defined. Why this occurs? So I had to delete and add again the ID column without specifying primary key – Depot

@hqtunes Don't have a slightest idea. Would probably help to give a tad more detail – Neese

Answer 2

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.

Figure

I think this answer needs to float to the top as not only does it remove the need to change the field definition, it also allows the change through with fk constraints on the table being altered - really helped me out! – Unconformity

Perfect ! For me dropping the primary key did not work, even if I alter the field to get rid of the auto increment. But this solution works fine ! – Adjudicate

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.

Kobi

Answer 4

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

Disapproval

Answer 5

In case you have composite primary key, do like this- ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);

Fervency

Indeed doing it in one line lets it work otherwise you can have 'there is a foreign key dependency' error. – Tabloid

Answer 6

To add primary key in the column.

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

To remove primary key from the table.

ALTER TABLE table_name DROP PRIMARY KEY;

Jerold

Answer 7

"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.

Erasion

Answer 8

I had same problem and beside some values inside my table. Although I changed my Primary Key with

ALTER TABLEuser_customer_permissionDROP PRIMARY KEY , ADD PRIMARY KEY (id)

problem continued on my server. I created new field inside the table I transfered the values into new field and deleted old one, problem solved!!

Orlene

Answer 9

First modify the column to remove the auto_increment field like this: alter table user_customer_permission modify column id int;

Next, drop the primary key. alter table user_customer_permission drop primary key;

Bulldog

Answer 10

First backup the database. Then drop any foreign key associated with the table. truncate the foreign key table.Truncate the current table. Remove the required primary keys. Use sqlyog or workbench or heidisql or dbeaver or phpmyadmin.

Karafuto

Answer 11

ALTER TABLE `table_name` ADD PRIMARY KEY( `column_name`);

Maxwellmaxy

Answer 12

the column has to be in the Indexes. If it isn't type the following command

ALTER TABLE <your_table_name> ADD CONSTRAINT UNIQUE(<your_column_name>);

then you can use

ALTER TABLE <your_table_name> DROP PRIMARY KEY, ADD PRIMARY KEY(id);

Impetus

Answer 13

Find the table in SQL manager right click it and select design, then right click the little key icon and select remove primary key.

Anny

While this post may solve the question, including a screenshot really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the same UI you're looking at. – Murrey