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)

score 12 · Accepted Answer · edited Nov 27 '17 at 17:12

12

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 .

edited Nov 27 '17 at 17:12

informatik01

16,038
10
74
104

answered Sep 29 '16 at 11:46

Mukesh Swami

415
2
11

1

Hi Mumesh , am I wrong or the two queries you have written are identical? Thank you for confirming o highlight the difference. – Robert Jul 17 '17 at 20:48
He is using two different columns `id` and `departure_id`, although only `departure_id` would be the correct one. – Manatax Oct 26 '17 at 19:37
Easy way to do this through PHPMYADMIN, see https://stackoverflow.com/a/32244787/1408137. The answer is for Oracle, but applicable to MySQL. – Sablefoste Feb 09 '22 at 02:32

Gordon Linoff · Answer 2 · 2016-09-29T12:08:56.063

8

In MySQL, you can also delete from multiple tables in one statement:

delete d, dt
    from departure d join
         departure_time dt
         on d.id = dt.departure_id
    where departure_date = '2016-09-30';

Another solution is to declare the foreign key relationship as on delete cascade. Then when you delete the row in the original table, the database will delete the related records in the second table.

edited Sep 29 '16 at 12:08

answered Sep 29 '16 at 11:30

Gordon Linoff

1,242,037
58
646
786

4

Thank you for your reply. But this did not work. Had to change dt.id to dt.departure_id Then it got an error Cannot delete or update a parent row: a foreign key constraint fails (`osl`.`departure_time`, CONSTRAINT `departure_time_departure_fk` FOREIGN KEY (`departure_id`) REFERENCES `departure` (`id`)) – TorK Sep 29 '16 at 11:34

score 1 · Answer 3 · answered Jan 01 '20 at 15:40

The multiple row delete failed for me because there were multiple rows in the second table referencing the first table. I was also unable to update the schema in order to add a foreign key constraint (as suggested in other answers).

Using IN like so worked:

DELETE FROM departure_time
WHERE departure_id IN (
        SELECT departure_id
        FROM departure
        WHERE departure_date = '2016-09-30'
    );
DELETE from departure
WHERE departure_date = '2016-09-30'

Mysql: delete rows in two tables with foreign keys

3 Answers3

Linked