CODEWITHSUNDEEP
mysql
TorK
TorK

Reputation: 617

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

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)

Upvotes: 8

Views: 30618

Answers (3)

Collin Krawll
Collin Krawll

Reputation: 2520

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'

Upvotes: 1

Mukesh Swami
Mukesh Swami

Reputation: 415

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 .

Upvotes: 12

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 9

Related Questions