mysql - 试图从 MySQL 表中删除主键

Question

编辑:不确定为什么将其标记为重复项。我得到的错误是不同的

我正在尝试删除主键定义，但由于某种原因收到错误消息。

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)

我是不是做错了什么？我只是不想在这个表中有更多的主键。

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)

Answer 1

您没有PRIMARY KEY；你有一个 UNIQUE 键。所以，你不能这样做:

ALTER TABLE `aux_sponsors` DROP PRIMARY KEY

相反，只是做

ALTER TABLE `aux_sponsors` DROP KEY `code`

DESC (a/k/a DESCRIBE) 不是真正的 MySQL 特性； 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.

在您的例子中，列 code 是 NOT NULL 并且是 UNIQUE 键中的唯一列，所以 DESC 将其显示为 PRI。由于此类问题，最好使用 SHOW INDEX找出表中键的类型。