4

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.

Gunaseelan
  • 2,494
  • 5
  • 36
  • 43
Adesh Pandey
  • 769
  • 1
  • 9
  • 22

3 Answers3

5

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

agim
  • 1,841
  • 12
  • 19
  • I have tried ALTER TABLE 'table_name' DROP PRIMARY KEY ALTER TABLE 'table_name' DROP PRIMARY KEY 'column_name' ALTER TABLE 'table_name' DROP PRIMARY KEY 'column_name',ADD PRIMARY KEY(column2); But none of the above commands did help me. – Adesh Pandey Apr 11 '13 at 08:30
  • @AdeshPandey remove first the `auto_increment` flag from the field. The above statement should work. – agim Apr 11 '13 at 08:40
4

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...
Vajk Hermecz
  • 5,413
  • 2
  • 34
  • 25
0

As mentioned, you need remove the FKs before. On MySQL, do like this:

ALTER TABLE `table_name` DROP FOREIGN KEY `id_name_fk`;

ALTER TABLE `table_name` DROP INDEX `id_name_fk`;
Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
dmand
  • 101
  • 8