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.