mysql 删除带有外键的表,MySQL：使用外键删除两个表中的行

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 .