Trying to remove a primary key from MySQL table
mysql, sql
Solution
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.
Problem
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) ```