CODEWITHSUNDEEP
phpmysql
Cleanshooter
Cleanshooter

Reputation: 2375

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

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.

Upvotes: 4

Views: 18190

Answers (4)

elPastor
elPastor

Reputation: 8996

This frustrated me to no end. When I ran:

mysql> describe my_table;

I got:

+-------------+------------+-----+
| Field       | Type       | Key |
+-------------+------------+-----+
| FIELD_ONE   | datetime   |     |
| FIELD_TWO   | char(6)    | PRI |
| FIELD_THREE | int        | PRI |
+-------------+------------+-----+

But when I ran ALTER TABLE my_table DROP PRIMARY KEY, I received the Can't DROP 'PRIMARY'; check that column/key exists error.

So instead, I ran:

mysql> SHOW INDEX FROM my_table;

And in the Key_name column there was an old key (using table columns that didn't even exist anymore!?). So I then ran:

mysql> ALTER TABLE my_table DROP old_key_that_does_not_exist;

...and it worked! I re-ran DESCRIBE my_table and got:

+-------------+------------+-----+
| Field       | Type       | Key |
+-------------+------------+-----+
| FIELD_ONE   | datetime   |     |
| FIELD_TWO   | char(6)    |     |
| FIELD_THREE | int        |     |
+-------------+------------+-----+

Running InnoDB version 5.7.23.

Upvotes: 0

user645280
user645280

Reputation:

Try flipping the order:

query = "ALTER TABLE `".$tablename."` ADD PRIMARY KEY(`".$name."`), DROP PRIMARY KEY;

The SQL looks like this, and is capable of changing the primary key of a table in one statement:

alter table `sometable` add primary key(`newcolumn`),  drop PRIMARY key;

Splitting it into 2 statements also works, but then you may lose some atomicity depending on your environment.

Looks like this also works, and isn't relying on strangeness in behavior to work correctly:

alter table sometable drop key `PRIMARY`, add primary key(`somecolumn`);

Upvotes: 1

nickb
nickb

Reputation: 59709

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`);

Upvotes: 6

Cymbals
Cymbals

Reputation: 1174

Try removing the "add primary key" and have that as an additional sql call.

Upvotes: 1

Related Questions