[MySQL]：从两个从属表中删除行 [英] [MySQL]: DELETE rows from two dependent tables

问题描述

表结构：
$ 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.

这篇关于[MySQL]：从两个从属表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！