MySQL 删除一些外键

MySQL Removing Some Foreign keys

提问by Drew

I have a table whose primary key is used in several other tables and has several foreign keys to other tables.

我有一个表，其主键用于其他几个表，并且有几个指向其他表的外键。

CREATE TABLE location (
   locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
   ...
) ENGINE = InnoDB;

CREATE TABLE assignment (
   assignmentID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   locationID INT NOT NULL,
   FOREIGN KEY locationIDX (locationID) REFERENCES location (locationID)
   ...
) ENGINE = InnoDB;

CREATE TABLE assignmentStuff (
   ...
   assignmentID INT NOT NULL,
   FOREIGN KEY assignmentIDX (assignmentID) REFERENCES assignment (assignmentID)
) ENGINE = InnoDB;

The problem is that when I'm trying to drop one of the foreign key columns (ie locationIDX) it gives me an error.

问题是，当我尝试删除其中一个外键列（即 locationIDX）时，它给了我一个错误。

"ERROR 1025 (HY000): Error on rename"

“错误 1025 (HY000)：重命名错误”

How can I drop the column in the assignment table above without getting this error?

如何删除上面分配表中的列而不会出现此错误？

回答by pugmarx

As explained here, seems the foreign key constraint has to be dropped by constraint nameand not the index name. The syntax is:

正如这里所解释的，似乎外键约束必须按约束名称而不是索引名称删除。语法是：

alter table footable drop foreign key fooconstraint

回答by zombat

The foreign keys are there to ensure data integrity, so you can't drop a column as long as it's part of a foreign key. You need to drop the key first.

外键用于确保数据完整性，因此您不能删除列，只要它是外键的一部分。您需要先放下钥匙。

I would think the following query would do it:

我认为以下查询可以做到：

ALTER TABLE assignmentStuff DROP FOREIGN KEY assignmentIDX;

回答by Karla Danitza Duran Memijes

As everyone said above, you can easily delete a FK. However, I just noticed that it can be necessary to drop the KEY itself at some point. If you have any error message to create another index like the last one, I mean with the same name, it would be useful dropping everything related to that index.

正如上面每个人所说，您可以轻松删除 FK。但是，我只是注意到在某些时候可能有必要删除 KEY 本身。如果您有任何错误消息来创建另一个索引，就像上一个索引一样，我的意思是使用相同的名称，删除与该索引相关的所有内容会很有用。

ALTER TABLE your_table_with_fk
  drop FOREIGN KEY name_of_your_fk_from_show_create_table_command_result,
  drop KEY the_same_name_as_above

回答by norodin

Check what's the CONSTRAINT name and the FOREIGN KEY name:

检查什么是 CONSTRAINT 名称和 FOREIGN KEY 名称：

SHOW CREATE TABLE table_name;

Remove both the CONSTRAINT name and the FOREIGN KEY name:

删除 CONSTRAINT 名称和 FOREIGN KEY 名称：

ALTER TABLE table_name
  DROP FOREIGN KEY the_name_after_CONSTRAINT,
  DROP KEY the_name_after_FOREIGN_KEY;

Hope this helps!

希望这可以帮助！

回答by Rock King Kirant

Here's a way to drop foreign key constraint, it will work. ALTER TABLE location.location_idDROP FOREIGN KEY location_ibfk_1;

这是一种删除外键约束的方法，它会起作用。更改表location。location_id删除外键location_ibfk_1；

回答by Pratik Gaurav

Hey I followed some sequence above, and found some solution.

嘿，我按照上面的一些顺序，找到了一些解决方案。

SHOW CREATE TABLE footable;

You will get FK Constrain Name like

您将获得 FK 约束名称，例如

ProjectsInfo_ibfk_1

Now you need to remove this constraints. by alter table commantd

现在您需要删除此约束。通过更改表命令

alter table ProjectsInfo drop foreign key ProjectsInfo_ibfk_1;

Then drop the table column,

然后删除表列，

alter table ProjectsInfo drop column clientId;

回答by Abinash Hota

You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.

如果您的表使用 InnoDB 引擎，您通常会收到此错误。在这种情况下，您必须删除外键，然后执行更改表并删除列。

But the tricky part is that you can't drop the foreign key using the column name, but instead you would have to find the name used to index it. To find that, issue the following select:

但棘手的部分是您不能使用列名删除外键，而是必须找到用于索引它的名称。要找到它，请发出以下选择：

SHOW CREATE TABLE region; This should show you a row ,at left upper corner click the +option ,the click the full text raio button then click the go .there you will get the name of the index, something like this:

显示创建表区域；这应该向您显示一行，在左上角单击 + 选项，单击全文 raio 按钮，然后单击转到。在那里您将获得索引的名称，如下所示：

CONSTRAINT region_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION Now simply issue an:

CONSTRAINT region_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION 现在只需发出：

alter table region drop foreign key region_ibfk_1;

更改表区域删除外键 region_ibfk_1;

or

或者

more simply just type:- alter table TableName drop foreign key TableName_ibfk_1;

更简单地输入：- alter table TableName drop外键 TableName_ibfk_1;

remember the only thing is to add _ibfk_1after your tablename to make like this:- TableName_ibfk_1

记住唯一的事情是 在你的表名之后添加 _ibfk_1来制作这样的：- TableName _ibfk_1

回答by Jyothi M

step1: show create table vendor_locations;

第1步： show create table vendor_locations;

step2: ALTER TABLE vendor_locations drop foreign key vendor_locations_ibfk_1;

第2步： ALTER TABLE vendor_locations drop foreign key vendor_locations_ibfk_1;

it worked for me.

它对我有用。

回答by Shailendra Mishra

Try this:

尝试这个：

alter table Documents drop
  FK__Documents__Custo__2A4B4B5E

回答by Ronald Wildenberg

You can not drop the foreign key column because it is being referenced from the table assignmentStuff. So you should first drop the foreign key constraint assignmentStuff.assignmentIDX.

您不能删除外键列，因为它是从表中引用的assignmentStuff。所以你应该首先删除外键约束assignmentStuff.assignmentIDX。

A similar question has already been asked here. Check also herefor more info.

一个类似的问题已经被问过here。也在这里查看更多信息。