MySQL reports a primary key but can not drop it from the table

Question

I am in the process of updating some tables in my database and I use the following code to check for a primary key and drop it then add a new primary key.

if(strpos($value,'PRIMARY') !== false)
{
    $dropit = "DROP PRIMARY KEY ,";
    $query = "ALTER TABLE `".$tablename."` ".$dropit." ADD PRIMARY KEY (`".$name."`);" ;
}

When I run I get this error:

1091 - Can't DROP 'PRIMARY'; check that column/key exists SQL=ALTER TABLE `my_table` DROP PRIMARY KEY , ADD PRIMARY KEY (`id`);

After some googling I have seen this issue show up on the MySQL bug reports form like 2006 so I would have figured it would be resolved by now. MySQL Bugs My version of My SQL is 5.1.44 so not super old... It seems that a column can look like a primary key but not be a primary key... so I am wondering if there is a better way to check before I try and remove to prevent the error.

I have also seen this on some Drupal forums but no clues are given on how to resolve...

If you think more of the surrounding code will help let me know and I will add.

Answer 1

It's trying to drop the key that's named PRIMARY. Although I'm not sure why it's not working (as the docs clearly show DROP PRIMARY KEY), you can try to change your SQL to something like:

ALTER TABLE `my_table` DROP KEY `key_name`, ADD PRIMARY KEY (`id`);