Deleting rows from 3 tables in MySQL table

Question

I've got 3 tables (I'll mention only attributes that are needed):

import id, date

import_head id, import_id

import_body id, import_head_id

import_head and import_body create one import item, import is created by dozens of import item Relations: import 1 <==> N *import_head* 1 <==> 1 *import_body* (ON DELETE CASCADE on every relation)

How can delete all import items when deleting row from import table?

When deleting import using DELETE FROM import WHERE id = ? error is thrown: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails - on import_head_id

Thanks a lot in advance

score 0 · Answer 1 · answered Mar 31 '14 at 13:11

0

Modify foreign key at child table and make when delete (CASCADE)
This should work for you

answered Mar 31 '14 at 13:11

Samy Massoud

4,295
2
35
48

Thanks for reply. Modify to what? Every is set to CASCADE when deleting. – peter.o Mar 31 '14 at 13:16
Do you try to delete id which is not exist in one of your tables ? – Samy Massoud Mar 31 '14 at 13:17
No, I does exist. I have checked it. – peter.o Mar 31 '14 at 13:18

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

You need to delete the records for the associated tables, before deleting the record from the primary key table. I would personally avoid cascade delete, because of performance implications, but it is your decision. Here is a thread talking about issues with cascade.

edited May 23 '17 at 11:57

Community

1
1

answered Mar 31 '14 at 13:11

Andrew Backes

1,884
4
21
37

Thanks a for reply. Well yes, I'm aware of this solution but I'd like to delete everything using one query (I'm using Doctrine2's remove function) – peter.o Mar 31 '14 at 13:17

score 0 · Accepted Answer · answered Apr 01 '14 at 06:09

0

I have changed entities in DB, so now there are only two tables:

import
import_item

I haven't found anything better, but it works.

answered Apr 01 '14 at 06:09

peter.o

3,460
7
51
77

Deleting rows from 3 tables in MySQL table

3 Answers3