How to remove primary key from a mysql table which is primary key and foreign key in the same table as well.?

Question

I have two different table having 20k entries each and mistakenly I have made summaryId as primary key and foreign key in the same table but now I want to remove the primary key constraint which is auto increament too. When I try drop primary key syntax it returns me an error :

#1025 - Error on rename of '.\tg#sql-a38_7f' to '.\tg\rest_web_availability_summary_pm' (errno: 150)

I tried the below query.

ALTER TABLE 'table_name' DROP PRIMARY KEY

If anybody has any idea please tell me how to remove primary key.

Answer 1

The problem is, that your field is auto_increment. You should remove auto_increment first and then drop the primary key.. so try this:

ALTER TABLE `mytable` CHANGE COLUMN `id` `id` INT(11) NOT NULL, DROP PRIMARY KEY;

Redefining the column without auto_increment removes it

Answer 2

I had the same problem, turned out that as it was referenced by other fields, mysql required the column to be unique, so I first added a unique constraint, and lived happily ever after:

alter table `mytable` add unique key `key` (`fieldname`);
alter table `mytable` drop primary key; -- which is fieldname...