Mysql:使用外键删除两个表中的行 [英] Mysql: delete rows in two tables with foreign keys

问题描述

我有两个带有数据的表. 我想删除两个表中的行. 但是我之间有外键. 我该怎么办?

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

最好的办法是拥有一个查询，该查询将删除所有行，并同时删除两个表中的行. 有没有办法消除约束/FK?

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?

在此示例中，说我想删除日期为2016-09-30的所有出发地

In this example, say I would like to delete all departures from date 2016-09-30

(删除出发时间:ID 2，出发时间:ID:2)

(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

或

DELETE FROM departure, departure_time
USING departure
INNER JOIN departure_time
WHERE departure_date = '2016-09-30'
      AND departure_time.departure_id = departure.id

或者您可以使用ON DELETE CASCADE，它将自动为您工作.

Or you can use ON DELETE CASCADE that will do work automatically for you .

这篇关于Mysql:使用外键删除两个表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！