MySQL 如何从mysql表中删除主键，主键和外键在同一个表中。？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15943946/
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
How to remove primary key from a mysql table which is primary key and foreign key in the same table as well.?
提问by Adesh Pandey
I have two different table having 20k entries each and mistakenly I have made summaryId as primary key and foreign key in the same table but now I want to remove the primary key constraint which is auto increament too. When I try drop primary key syntax it returns me an error :
我有两个不同的表，每个表有 20k 个条目，我错误地将 summaryId 设为同一个表中的主键和外键，但现在我想删除也是自动增加的主键约束。当我尝试删除主键语法时，它会返回一个错误：
#1025 - Error on rename of '.\tg#sql-a38_7f' to '.\tg\rest_web_availability_summary_pm' (errno: 150)
#1025 - 将 '.\tg#sql-a38_7f' 重命名为 '.\tg\rest_web_availability_summary_pm' 时出错（错误号：150）
I tried the below query.
我尝试了以下查询。
ALTER TABLE 'table_name' DROP PRIMARY KEY
If anybody has any idea please tell me how to remove primary key.
如果有人有任何想法，请告诉我如何删除主键。
回答by agim
The problem is, that your field is auto_increment. You should remove auto_increment first and then drop the primary key.. so try this:
问题是，你的领域是auto_increment. 您应该先删除 auto_increment 然后删除主键..所以试试这个：
ALTER TABLE `mytable` CHANGE COLUMN `id` `id` INT(11) NOT NULL, DROP PRIMARY KEY;
Redefining the column without auto_increment removes it
重新定义没有 auto_increment 的列会删除它
回答by Vajk Hermecz
I had the same problem, turned out that as it was referenced by other fields, mysql required the column to be unique, so I first added a unique constraint, and lived happily ever after:
我也遇到了同样的问题，原来是因为被其他字段引用了，mysql要求该列是唯一的，所以我先加了一个唯一约束，从此过上了幸福的生活：
alter table `mytable` add unique key `key` (`fieldname`);
alter table `mytable` drop primary key; -- which is fieldname...
回答by Kostandy
As mentioned, you need remove the FKs before. On MySQL, do like this:
如前所述，您需要删除FKs 之前。在 MySQL 上，这样做：
ALTER TABLE `table_name` DROP FOREIGN KEY `id_name_fk`;
ALTER TABLE `table_name` DROP INDEX `id_name_fk`;
