blocks|key|342194|text|正如here所解释的，外键约束似乎必须通过约束名称而不是索引名称来删除。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|342195|语法为：|342196|ALTER+TABLE+footable+DROP+FOREIGN+KEY+fooconstraint;|code-block|syntax|javascript|342197|entityMap|0|LINK|mutability|MUTABLE|url|http://bugs.mysql.com/bug.php?id=14347^0|L|4|2|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|6|7|10|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

As explained <a href="http://bugs.mysql.com/bug.php?id=14347" rel="noreferrer">here</a>, seems the foreign key constraint has to be dropped by constraint name and not the index name.
The syntax is:
<pre><code>ALTER TABLE footable DROP FOREIGN KEY fooconstraint;
</code></pre>

blocks|key|3326871|text|外键是用来确保数据完整性的，所以只要列是外键的一部分，就不能删除它。你需要先放下钥匙。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3326872|我认为下面的查询可以做到这一点：|3326873|ALTER+TABLE+assignmentStuff+DROP+FOREIGN+KEY+assignmentIDX;|code-block|syntax|javascript|3326874|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>ALTER TABLE assignmentStuff DROP FOREIGN KEY assignmentIDX;
</code></pre>

blocks|key|340834|text|正如每个人上面所说的，你可以很容易地删除一个FK。然而，我只是注意到在某些情况下可能有必要丢弃密钥本身。如果您有任何错误消息来创建另一个类似上一个索引的索引，我的意思是使用相同的名称，删除与该索引相关的所有内容将非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|340835|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|code-block|syntax|javascript|340836|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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
</code></pre>

blocks|key|3326938|text|检查约束名称和外键名称：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3326939|SHOW+CREATE+TABLE+table_name;|code-block|syntax|javascript|3326940|删除约束名称和外键名称：|3326941|ALTER+TABLE+table_name
++DROP+FOREIGN+KEY+the_name_after_CONSTRAINT,
++DROP+KEY+the_name_after_FOREIGN_KEY;|3326942|希望这能有所帮助！|3326943|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>SHOW CREATE TABLE table_name;
</code></pre>

Remove both the CONSTRAINT name and the FOREIGN KEY name:

<pre><code>ALTER TABLE table_name
 DROP FOREIGN KEY the_name_after_CONSTRAINT,
 DROP KEY the_name_after_FOREIGN_KEY;
</code></pre>

Hope this helps!

blocks|key|3326950|text|嘿，我遵循了上面的一些顺序，找到了一些解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3326951|SHOW+CREATE+TABLE+footable;|code-block|syntax|javascript|3326952|您将获得如下的FK约束名称|3326953|ProjectsInfo_ibfk_1|3326954|现在，您需要删除此约束。通过alter+table命令|3326955|alter+table+ProjectsInfo+drop+foreign+key+ProjectsInfo_ibfk_1;|3326956|然后删除表列，|3326957|alter+table+ProjectsInfo+drop+column+clientId;|3326958|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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

<pre><code>SHOW CREATE TABLE footable;
</code></pre>

You will get FK Constrain Name like

<pre><code>ProjectsInfo_ibfk_1
</code></pre>

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

<pre><code>alter table ProjectsInfo drop foreign key ProjectsInfo_ibfk_1;
</code></pre>

Then drop the table column,

<pre><code>alter table ProjectsInfo drop column clientId;
</code></pre>

blocks|key|3114115|text|这里有一个去掉外键约束的方法，它会起作用的。ALTER+TABLE+location。location_id+DROP+FOREIGN+KEY+location_ibfk_1；|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3114116|entityMap^0|Y|8|17|B|20|F|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

Here's a way to drop foreign key constraint, it will work.
ALTER TABLE <code>location</code>.<code>location_id</code> 
DROP FOREIGN KEY <code>location_ibfk_1</code>;

blocks|key|3326837|text|不能删除外键列，因为它正被表assignmentStuff引用。因此，您应该首先删除外键约束assignmentStuff.assignmentIDX。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3326838|类似的问题已经被问到了here。有关更多信息，请查看here。|3326839|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-mean|1|http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/^0|E|F|1A|T|0|B|4|0|Q|4|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@]|D|@$9|X|A|Y|1|Z]|$9|10|A|11|1|12]]|E|$]]|$1|H|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]]]

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

A similar question has already been asked <a href="https://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-mean">here</a>. Check also <a href="http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/" rel="nofollow noreferrer">here</a> for more info.

blocks|key|340830|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|340831|alter+table+Documents+drop
++FK__Documents__Custo__2A4B4B5E|code-block|syntax|javascript|340832|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>alter table Documents drop
 FK__Documents__Custo__2A4B4B5E
</code></pre>

blocks|key|345732|text|step1：show+create+table+vendor_locations;|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|345733|step2：ALTER+TABLE+vendor_locations+drop+foreign+key+vendor_locations_ibfk_1;|345734|这对我很管用。|345735|entityMap^0|6|Z|0|6|1Y|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|K|$]]

step1: <code>show create table vendor_locations;</code>

step2: <code>ALTER TABLE vendor_locations drop foreign key vendor_locations_ibfk_1;</code>

it worked for me.

blocks|key|3114160|text|首先需要通过该查询获取实际的约束名称|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3114161|SHOW+CREATE+TABLE+TABLE_NAME|code-block|syntax|javascript|3114162|此查询将导致外键的约束名称，现在下面的查询将删除它。|3114163|ALTER+TABLE+TABLE_NAME+DROP+FOREIGN+KEY+COLUMN_NAME_ibfk_1|3114164|上面约束名称中的最后一个数字取决于表中有多少个外键|3114165|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

first need to get actual constrain name by this query

<pre><code>SHOW CREATE TABLE TABLE_NAME
</code></pre>

This query will result constrain name of the foreign key, now below query will drop it.

<pre><code>ALTER TABLE TABLE_NAME DROP FOREIGN KEY COLUMN_NAME_ibfk_1
</code></pre>

last number in above constrain name depends how many foreign keys you have in table

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

<pre><code>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;
</code></pre>

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

<blockquote>
 "ERROR 1025 (HY000): Error on rename"
</blockquote>

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

MySQL Removing Some Foreign keys

数据完整性

我有一个表，它的主键在其他几个表中使用，并且有几个指向其他表的外键。CREATE TABLE location (   locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY   ...) ENGINE = InnoDB;CREATE TABLE assignment (   assignmentID INT NOT NULL AUTO_INCREME

问MySQL删除一些外键
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL删除一些外键EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL删除一些外键
EN