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

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 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 =

)

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.