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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With