[MySQL]: DELETE rows from two dependent tables

Question

Hi -

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)

Answer 1

Transaction <- Purchase -> Item

Your problem is that you are trying to delete the Transaction before deleting the purchase, the correct way to do it is deleting the purchase and only then the transaction, that, or you could use ON DELETE CASCADE on your foreign keys, it seems that you are using ON DELETE RESTRICT.

See here: http://dev.mysql.com/doc/refman/5.0/es/innodb-foreign-key-constraints.html

Besides, if you wan't to be sure that's the reason you should give the complete error, the DBMS usually tells you what foreign key violation is raising the error.

UPDATE: your error made it clear, the problem is that you are deleting the Purchase first and then the Transaction, you need to do it backwards or set ON DELETE CASCADE on your foreign key constraint

Answer 2

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.