从具有外键约束的MySQL表中删除 [英] Deleting from a MySQL table with foreign key constraints

问题描述

我有两个表，加载了一些数据，两个引用。表B引用表A的主键。我手动尝试删除表B中存在的表B中的一些表行，我得到：

I have two tables, with some data loaded in it and the two reference each other. Table B references the primary key of Table A. I manually attempt to delete some table rows from Table A that are present in Table B, and I get this:

＃1451 - 无法删除或更新父行：外键约束失败（ TableA 。 TableB ，CONSTRAINT TableB_ibfk_2 FOREIGN KEY（ column ）REFERENCES flashcard （ primaryKeyColumn ））

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (TableA.TableB, CONSTRAINT TableB_ibfk_2 FOREIGN KEY (column) REFERENCES flashcard (primaryKeyColumn))

我不太确定这里有什么。从我的理解，如果我删除TableA的一些行，那么他们应该自动删除表B中存在的相应行（即如果它们存在于表B），但显然这不是这里的情况，它提示上述错误

I am not quite sure what's up here. From what I understand,if I delete some rows of TableA then they should automatically also delete the corresponding rows present in Table B (i.e if they are present in Table B) but clearly this isn't the case here and it's prompting the above error.

任何人都想打开我的头，让我知道为什么？

Anybody wanna whack the side of my head and let me know why ?

推荐答案

不幸的是，你认为应该发生的自动删除不会发生。您需要手动删除表B中包含您希望删除的表A中的ID的行。

Unfortunately, the automatic deletion that you think should happen does not happen. You would need to manually delete the rows in Table B that contain the ID from the row in Table A that you wish to remove.

尝试添加 ON DELETE CASCADE 到表B中的外键列定义。

Try adding ON DELETE CASCADE to your foreign key column definition in Table B.

如果您使用Hibernate或其他ORM工具，它将具有级联机制会自动处理这个。但是在这里，由于您直接与数据库交互，因此您必须记住，如果所述行的ID作为外键存在于任何其他表中，则无法删除该行（在您的情况下，A的主键存在于B中的一些行）。除非你指定ON DELETE CASCADE属性。

If you are using Hibernate, or some other ORM tool, it would have a "cascade" mechanism that would handle this automatically. But here since you are interfacing with the database directly, you must keep in mind that a row cannot be deleted if the ID of said row exists in any other table as a foreign key (as in your case, the primary key of A exists in some of the rows in B). Unless of course you specify the ON DELETE CASCADE property.

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