I have two tables with data.
I want to delete rows in both tables.
But I have foreign keys between them.
How can I do this?
departure
id departure_date
1 2016-09-29
2 2016-09-30
departure_time
id departure_id (fk)
1 1
2 2
the best thing would be to have a query that gets all rows to be deleted, and deletes rows in both tables at the same time.
Is there a way to do this without removing constraints/FK?
In this example, say I would like to delete all departures from date 2016-09-30
(delete departure: id 2 and departure_time: id: 2)
解决方案
Please try this, hope it will help.
DELETE FROM departure, departure_time
USING departure
INNER JOIN departure_time
WHERE departure_date = '2016-09-30'
AND departure_time.id = departure.id
Or
DELETE FROM departure, departure_time
USING departure
INNER JOIN departure_time
WHERE departure_date = '2016-09-30'
AND departure_time.departure_id = departure.id
Or you can use ON DELETE CASCADE that will do work automatically for you .