Trying to remove a primary key from MySQL table

Question

Edit: Not sure why this is marked as a duplicate. The error I am getting is different

I am trying to remove a primary key definition but am receiving an error for some reason.

mysql> ALTER TABLE `aux_sponsors` DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
mysql> desc aux_sponsors;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| unit        | varchar(8)   | NO   |     | MF      |       |
| code        | varchar(32)  | NO   | PRI | NULL    |       |
| userid      | varchar(32)  | NO   |     |         |       |
| fullName    | varchar(64)  | NO   |     |         |       |
| department  | varchar(255) | NO   |     |         |       |
| description | varchar(255) | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

Am I doing something wrong here? I simply want no more primary key in this table.

mysql> SHOW CREATE TABLE aux_sponsors;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aux_sponsors | CREATE TABLE `aux_sponsors` (
  `unit` varchar(8) NOT NULL DEFAULT 'MF',
  `code` varchar(32) NOT NULL,
  `userid` varchar(32) NOT NULL DEFAULT '',
  `fullName` varchar(64) NOT NULL DEFAULT '',
  `department` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL,
  UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Can you try `ALTER TABLE \`aux_sponsors\` DROP KEY 'PRIMARY';`? — ForguesR, Jul 09 '14 at 16:38
@ForguesR `ALTER TABLE `aux_sponsors` DROP KEY `PRIMARY`; ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists ` Still the same issue. — ComputerLocus, Jul 09 '14 at 16:55
You don't have a primary key; you have a unique key. So, no need to do `ALTER TABLE aux_sponsors DROP PRIMARY KEY`. Just do `ALTER TABLE aux_sponsors DROP KEY code`. I'd post this as an answer, but I can't until this gets reopened. — elixenide, Jul 09 '14 at 17:08
@Ed Cottrell In your answer can you explain why we see `PRI` in the table DESC? This is really misleading. — ForguesR, Jul 09 '14 at 17:30
Yes; there's a reason in the docs, which I will post as soon as this reopens. — elixenide, Jul 09 '14 at 17:33

score 7 · Accepted Answer · answered Jul 09 '14 at 17:36

7

You don't have a PRIMARY KEY; you have a UNIQUE key. So, you can't do this:

ALTER TABLE `aux_sponsors` DROP PRIMARY KEY

Instead, just do

ALTER TABLE `aux_sponsors` DROP KEY `code`

DESC (a/k/a DESCRIBE) is not a true MySQL feature; according to the docs, "The DESCRIBE statement is provided for compatibility with Oracle."

More from the documentation:

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

In your case, the column code is NOT NULL and is the only column in a UNIQUE key, so DESC is showing it as PRI. Because of this type of problem, it's better to use SHOW INDEX to find out the types of keys on a table.

answered Jul 09 '14 at 17:36

elixenide

44,308
16
74
100

Thanks for the answer, I was trying out the answer found in the other question about pkeys, and I was having no success, that is why I created this questions as the issue was different. Thank you for noticing the issue and giving me a great informative answer! I have been using `desc` as I am checking a lot of tables and it was fast to type in. – ComputerLocus Jul 09 '14 at 17:44
1

Glad to help! I have run into this problem myself in the past. – elixenide Jul 09 '14 at 17:45
2

That would seem to be a really bad idea on the part of MySQL -- for exactly the reason that it makes it difficult to understand the actual definition of your table. – Larry Lustig Jul 09 '14 at 17:56
1

@LarryLustig yes, exactly. I am still fairly new to MySQL and I had no idea there even was a `SHOW INDEX` and I thought just using `describe` was the way you do it. Not very good for the new users especially. – ComputerLocus Jul 09 '14 at 17:58

Trying to remove a primary key from MySQL table

1 Answers1