Mysql: delete rows in two tables with foreign keys

Question

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)

Answer 1

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 .