CODEWITHSUNDEEP
sql
Pac Rauce
Pac Rauce

Reputation: 229

How can I delete rows from two tables?

I have table1 and table2. I need to delete from table1 all rows. When I try DELETE FROM table1 this error appear

1451 - Cannot delete or update a parent row: a foreign key constraint fails (db_name.table2, CONSTRAINT table2_ibfk_4 FOREIGN KEY (table1_id) REFERENCES table1 (id))

How can I delete all rows in table1, and all rows in table2, which table1_id match with id from table1?

Upvotes: 0

Views: 53

Answers (3)

Robin
Robin

Reputation: 13

Your error indicates an issue where there is a foreign key reference from table2 to table1. To solve this you have a few options:

  • Delete the records referring to the table1 record from table2, before deleting the record in table1
  • Add a ON DELETE CASCADE to the foreign key

Upvotes: 1

Tarek
Tarek

Reputation: 3798

Start by deleting table2 then table1

or add before deleting :

SET FOREIGN_KEY_CHECKS=0;

then after deletion completed : 

SET FOREIGN_KEY_CHECKS=1;

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

DELETE FROM table2 WHERE EXISTS
(
    SELECT 1 FROM table1 WHERE table1.id = table2.table1_id
);
DELETE FROM table1;

Upvotes: 1

Related Questions