用外键删除列 [英] Dropping a column with a foreign key

问题描述

在具有inno_db引擎的mysql数据库上，

On my mysql DB with inno_db engine,

我有一个带外键的表. 我想删除该列(当然还有外键和关联的索引-我不需要整个列！)

I have a table with a foreign key. I want to drop the column (along with the foreign key and the associated index of course - i don't need the whole column!)

现在，简单地将其删除会产生一个错误: 常规错误:1025将.\ road_dmy#sql-19d8_2be"重命名为.\ road_dmy \ contact"(错误号:150)时出现错误

Now, simply dropping it yields an error: General error: 1025 Error on rename of '.\road_dmy#sql-19d8_2be' to '.\road_dmy\contact' (errno: 150)

听起来这是一个已知问题. http://bugs.mysql.com/bug.php?id=15317

It sounds like this is a known issue. http://bugs.mysql.com/bug.php?id=15317

但是无论如何，我应该怎么做才能删除此列?我非常确定，否则没有人会使用此数据库

But anyway, what should i do to drop this column? I'm very sure it's possible nobody would use this DB otherwise

(还有b.t.w.我怎么知道上面神秘错误消息的真实细节?)

(and b.t.w. how can I know the true details of the mysterious error message above?)

推荐答案

您必须先删除密钥.我不知道您的表的名称，但是我将通过示例为您提供一般策略.假设您具有以下2个InnoDB表:

You must drop the key first. I don't know the names of your tables but I'll give you the general strategy by example. Suppose you have the following 2 InnoDB tables:

CREATE TABLE `A` (
   `id` int(10) unsigned NOT NULL auto_increment,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `B` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `a_id` int(10) unsigned NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `a_id` (`a_id`),
    CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB;

您可以使用以下命令将表B中的a_id列删除:

You can drop the a_id column in table B using the following command:

alter table B drop foreign key b_ibfk_1, drop column a_id;

这篇关于用外键删除列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！