从具有外键约束的 MySQL 表中删除

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

问题描述

我有两个表，其中加载了一些数据，并且两个表相互引用.表 B 引用了表 A 的主键.我手动尝试从表 A 中删除表 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_2FOREIGN 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 表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！