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?

Quassnoi · Accepted Answer · 2013-09-23T20:19:18.490

347

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;

edited Sep 23 '13 at 20:19

answered Jan 21 '10 at 17:23

Quassnoi

413,100
91
616
614

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; – markb Jan 21 '10 at 17:28
`@markb`: if you restore the primary key, you sure may revert it back to `AUTO_INCREMENT`. – Quassnoi Jan 21 '10 at 17:34
2

what does this mean *autoincrement column to be a leftmost part of the PRIMARY KEY.* ? – Arup Rakshit Sep 23 '13 at 19:38
1

@ArupRakshit: http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html *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.* – Quassnoi Sep 23 '13 at 20:17
it says PRIMARY is not defined. Why this occurs? So I had to delete and add again the ID column without specifying primary key – Bengi Besçeli Nov 09 '17 at 14:11
@hqtunes Don't have a slightest idea. Would probably help to give a tad more detail – Quassnoi Nov 10 '17 at 08:13

score 144 · Answer 2 · answered Jan 21 '10 at 17:51

144

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.

answered Jan 21 '10 at 17:51

mluebke

8,588
7
35
31

6

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! – tomfumb May 10 '11 at 17:42
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 ! – user2447161 Dec 04 '19 at 01:07

score 16 · Answer 3 · edited Oct 06 '21 at 12:28

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.

score 13 · Answer 4 · answered Jan 21 '10 at 17:25

13

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

answered Jan 21 '10 at 17:25

Alix Axel

151,645
95
393
500

score 11 · Answer 5 · answered Nov 20 '15 at 08:58

11

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

answered Nov 20 '15 at 08:58

Visvendra Singh Rajpoot

1,213
12
21

Indeed doing it in one line lets it work otherwise you can have 'there is a foreign key dependency' error. – belka Aug 29 '23 at 11:07

score 9 · Answer 6 · edited May 08 '18 at 06:41

9

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;

edited May 08 '18 at 06:41

coder

8,346
16
39
53

answered May 08 '18 at 05:45

sumit katiyar

191
2
7

score 5 · Answer 7 · answered Jan 21 '10 at 18:30

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

score 4 · Answer 8 · answered Jul 22 '17 at 12:01

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!!

score 3 · Answer 9 · answered Sep 04 '14 at 05:57

3

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;

answered Sep 04 '14 at 05:57

John Gunderman

31
3

score 0 · Answer 10 · answered Aug 14 '16 at 20:53

0

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.

answered Aug 14 '16 at 20:53

Noby Nirmal

353
2
5

score 0 · Answer 11 · edited Oct 24 '19 at 08:25

0

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

edited Oct 24 '19 at 08:25

Mickaël Leger

3,426
2
17
36

answered Oct 24 '19 at 03:27

Malith Ileperuma

926
11
27

score -1 · Answer 12 · answered Apr 09 '18 at 13:49

-1

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

answered Apr 09 '18 at 13:49

Noob Helper

1

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. – Pirate X Apr 09 '18 at 14:44

Remove Primary Key in MySQL

12 Answers12

Linked