删除 MySQL 中的主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2111291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove Primary Key in MySQL
提问by markb
I have the following table schema which maps user_customers to permissions on a live MySQL database:
我有以下表架构，它将 user_customers 映射到实时 MySQL 数据库的权限：
mysql> describe user_customer_permission;
+------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_customer_id | int(11) | NO | PRI | NULL | |
| permission_id | int(11) | NO | PRI | NULL | |
+------------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
I would like to remove the primary keys for user_customer_id and permission_id and retain the primary key for id.
我想删除 user_customer_id 和 permission_id 的主键并保留 id 的主键。
When I run the command:
当我运行命令时：
alter table user_customer_permission drop primary key;
I get the following error:
我收到以下错误：
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
How can I drop a column's primary key?
如何删除列的主键？
回答by Quassnoi
Without an index, maintaining an autoincrement column becomes too expensive, that's why MySQLrequires an autoincrement column to be a leftmost part of an index.
如果没有索引，维护自动增量列会变得太昂贵，这就是为什么MySQL要求自动增量列作为索引最左边的部分。
You should remove the autoincrement property before dropping the key:
您应该在删除键之前删除 autoincrement 属性：
ALTER TABLE user_customer_permission MODIFY id INT NOT NULL;
ALTER TABLE user_customer_permission DROP PRIMARY KEY;
Note that you have a composite PRIMARY KEYwhich covers all three columns and idis not guaranteed to be unique.
请注意，您有一个PRIMARY KEY涵盖所有三列的组合，并且id不能保证是唯一的。
If it happens to be unique, you can make it to be a PRIMARY KEYand AUTO_INCREMENTagain:
如果碰巧是唯一的，你可以把它是一个PRIMARY KEY和AUTO_INCREMENT再次：
ALTER TABLE user_customer_permission MODIFY id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;
回答by mluebke
One Line:
一条线：
ALTER TABLE `user_customer_permission` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` )
You will also not lose the auto-increment and have to re-add it which could have side-effects.
您也不会丢失自动增量，而必须重新添加它，这可能会产生副作用。
回答by Jay
I believe Quassnoi has answered your direct question. Just a side note: Maybe this is just some awkward wording on your part, but you seem to be under the impression that you have three primary keys, one on each field. This is not the case. By definition, you can only have one primary key. What you have here is a primary key that is a composite of three fields. Thus, you cannot "drop the primary key on a column". You can drop the primary key, or not drop the primary key. If you want a primary key that only includes one column, you can drop the existing primary key on 3 columns and create a new primary key on 1 column.
我相信 Quassnoi 已经回答了您的直接问题。只是一个旁注：也许这只是您的一些尴尬措辞，但您似乎认为您有三个主键，每个字段一个。不是这种情况。根据定义，您只能有一个主键。您在这里拥有的是一个由三个字段组成的主键。因此，您不能“删除列上的主键”。您可以删除主键，也可以不删除主键。如果您想要一个仅包含一列的主键，您可以删除 3 列上的现有主键并在 1 列上创建一个新主键。
回答by Alix Axel
ALTER TABLE `user_customer_permission` MODIFY `id` INT;
ALTER TABLE `user_customer_permission` DROP PRIMARY KEY;
回答by Visvendra Singh Rajpoot
In case you have composite primary key, do like this- ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);
如果您有复合主键，请这样做- ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (col_name1, col_name2);
回答by Erwin Smout
"if you restore the primary key, you sure may revert it back to AUTO_INCREMENT"
“如果您恢复主键，您肯定可以将其恢复为 AUTO_INCREMENT”
There should be no question of whether or not it is desirable to "restore the PK property" and "restore the autoincrement property" of the ID column.
ID列的“恢复PK属性”和“恢复自增属性”应该是没有问题的。
Given that it WAS an autoincrement in the prior definition of the table, it is quite likely that there exists some program that inserts into this table without providing an ID value (because the ID column is autoincrement anyway).
鉴于它是表的先前定义中的自动增量，很可能存在一些程序在不提供 ID 值的情况下插入到该表中（因为无论如何 ID 列都是自动增量的）。
Any such program's operation will break by not restoring the autoincrement property.
任何此类程序的操作都将因不恢复自动增量属性而中断。
回答by John Gunderman
First modify the column to remove the auto_increment field like this: alter table user_customer_permission modify column id int;
首先修改列以删除 auto_increment 字段，如下所示：alter table user_customer_permission modify column id int;
Next, drop the primary key. alter table user_customer_permission drop primary key;
接下来，删除主键。更改表 user_customer_permission 删除主键；
回答by Malith Ileperuma
ALTER TABLE `table_name` ADD PRIMARY KEY( `column_name`);
回答by sumit katiyar
To addprimary key in the column.
要添加列主键。
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
To removeprimary key from the table.
要删除从表的主键。
ALTER TABLE table_name DROP PRIMARY KEY;
回答by Sam
I had same problem and beside some values inside my table. Although I changed my Primary Key with
我有同样的问题，除了我表中的一些值。虽然我改变了我的主键
ALTER TABLEuser_customer_permissionDROP PRIMARY KEY , ADD PRIMARY KEY (id)
ALTER TABLEuser_customer_permission DROP PRIMARY KEY , ADD PRIMARY KEY (id)
problem continued on my server. I created new field inside the table I transfered the values into new field and deleted old one, problem solved!!
问题在我的服务器上继续存在。我在表中创建了新字段我将值转移到新字段并删除了旧字段，问题解决了！！
