[MySQL]: DELETE rows from two dependent tables

cascading-deletes, foreign-keys, mysql

Solution

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.

Problem

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) ```

Original source