[MySQL]：从两个从属表中删除行

本文介绍了[MySQL]：从两个从属表中删除行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

表结构： $ b $我们试图删除两个依赖表中的所有行。 b

交易 -Transaction_ID（主要） - 时间戳 购买 -Item_ID -Transaction_ID -Purchase_ID（主要） 项目 -Item_ID（主要） -Client_ID

我想从transaction / purchase中删除与item中的Client_ID匹配的所有行。听起来很简单...即使我可以包装我的新手头脑... $ p $ 删除dbName.t从 dbName.Transaction t JOIN dbName.Purchase p ON p.Transaction_ID = t.Transaction_ID JOIN dbName.Item ON p.Item_ID = i.Item_ID WHERE Client_ID = 1

$ b $我得到这个错误外键约束失败... - 我'你们中的许多人并不感到惊讶。 是购买使用t.Transaction_ID的问题吗？（因此，这个外键会失败）

或者是否有其他t.Transaction_ID依赖的数据在这个表中（我还没有找到）。 编辑： COMPLETE ERROR

无法删除或更新父项行：外键约束失败（`ItemTracker_dbo / Purchase`，CONSTRAINT`FK_Purchase_Transaction` FOREIGN KEY（`Transaction_ID`）RE FERENCES`Transaction`（`Transaction_ID`） ON DELETE NO ACTION ON UPDATE CASCADE）

解决方案

只要依赖记录仍然存在于另一个表中，则无法从表中删除。在你的情况下，依赖是这样的

Transaction< - Purchase - >项目

所以您需要先删除任何购物，然后才能删除交易。 $ b

作为这种两步法的替代方法，我建议设置一个

删除事务 WHERE Transaction_ID IN（ SELECT Transaction_ID FROM 购买INNER JOIN Item ON Item.Item_ID = Purchase.Item_ID WHERE Item.Client_ID =<您的客户ID> ）

请注意，这将删除任何 Transaction （并且通过CASCADE，任何购买），其中有一个相关的项目与匹配的 Client_ID ，无论是否存在其中的任何其他项目。如果这不是你想要的，这个问题需要改进。

I am attempting to delete all rows in two dependent tables based on a third tables ID.

Table structure:

Transaction -Transaction_ID (primary) -Timestamp Purchase -Item_ID -Transaction_ID -Purchase_ID (primary) Item -Item_ID (primary) -Client_ID

I would like to delete all rows from transaction/purchase that match the Client_ID in item. Sounds simple enough... even I can wrap my novice mind around that...

DELETE dbName.t FROM dbName.Transaction t JOIN dbName.Purchase p ON p.Transaction_ID = t.Transaction_ID JOIN dbName.Item i ON p.Item_ID = i.Item_ID WHERE Client_ID = 1

Nope...

I get this error foreign key constraint fails... - I'm sure many of you are not surprised.

Is the issue that Purchase uses t.Transaction_ID? - (thus, this foreign key would fail)

OR is there likely other t.Transaction_ID dependent data in this table (i haven't found any).

EDIT: COMPLETE ERROR

Cannot delete or update a parent row: a foreign key constraint fails (`ItemTracker_dbo/Purchase`, CONSTRAINT `FK_Purchase_Transaction` FOREIGN KEY (`Transaction_ID`) REFERENCES `Transaction` (`Transaction_ID`) ON DELETE NO ACTION ON UPDATE CASCADE)

解决方案

You can't delete from a table as long as dependent records still exist in another table. In your case, the dependency goes like this

Transaction <- Purchase -> Item

So you need to delete any purchases first before you can delete transactions.

As an alternative to that two-step approach, I would recommend setting up an ON DELETE CASCADE constraint and go with this:

DELETE Transaction WHERE Transaction_ID IN ( SELECT Transaction_ID FROM Purchase INNER JOIN Item ON Item.Item_ID = Purchase.Item_ID WHERE Item.Client_ID = <your Client ID here> )

Beware that this deletes any Transaction (and, through CASCADE, any Purchase) where there is a dependent Item with a matching Client_ID, regardless of whether there are any other items in it. If this is not what you want, the question needs to be refined.