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.