SQL DELETE from multiple tables

Question

When I try to execute this delete query, SQL gives me this error:

"Cannot delete or update a parent row: a foreign key constraint fails (carpooling.pedido, CONSTRAINT pedido_ibfk_1 FOREIGN KEY (ID_ROTA) REFERENCES rota (ID_ROTA))"

Query:

DELETE rota,rota_alerta,pedido FROM rota
    LEFT OUTER JOIN pedido ON rota.ID_ROTA=pedido.ID_ROTA
    LEFT OUTER JOIN rota_alerta ON rota.ID_ROTA=rota_alerta.ID_ROTA
    WHERE rota.ID_UTILIZADOR=26;

I'm trying to delete all rows from "pedido" and "rota_alerta" that are linked to a row of table "rota" by "ID_ROTA". This "ID_ROTA" depends from the number of rows that have the same "ID_UTILIZADOR" (in this case, 26) on table "rota".

Also, I want to delete all rows of "rota" that have the specified "ID_UTILIZADOR".

How could I fix this?

EDIT

I forgot to say that I don't want to use "cascade constraints" because there are cases where I should do a "delete rota where ID_UTILIZADOR=26" (as mentioned in a comment) and avoid to delete the child rows if they exist.

jenson-button-event · Accepted Answer · 2011-12-13T12:23:05.020

3

i'd delete my child collections first:

delete rota_alerta where rota_id in(select id_rota from rota where ID_UTILIZADOR=26)
delete pedido where rota_id in(select rota_id from rota where ID_UTILIZADOR=26)
delete rota where ID_UTILIZADOR=26

edited Dec 13 '11 at 12:23

answered Dec 13 '11 at 12:16

jenson-button-event

18,101
11
89
155

@CristianoSantos this is essentially hand-coding a cascading delete and will give you the same issues as described in your comment – Pleun Dec 13 '11 at 12:29
@Pleun But in the other query where I should not delete the child rows, I just need to execute something like "delete rota where ID_UTILIZADOR=26" – Cristiano Santos Dec 13 '11 at 12:37
Yes, you got a point there. Will cause your delete (in the other query) to fail in some cases but if that is what you want this solution is fine. – Pleun Dec 13 '11 at 12:45

Pleun · Answer 2 · 2011-12-13T12:25:48.737

1

Change your foreign key constraint to CASCADE

Well, from the comment you gave it now has become clear that the cascading delete is conditional.

This means you either need to code out the exact conditions. Either in your client or in a stored procedure.

Or still change to cascade and add triggers again to prevent the deletion (not sure if that is possible in MySql)

edited Dec 13 '11 at 12:25

answered Dec 13 '11 at 12:16

Pleun

8,856
2
30
50

Due to security risks, I can't do it =S. – Cristiano Santos Dec 13 '11 at 12:18
In some cases, I do not want to give the permission of deleting rows from "rota" if childs rows exist and this query should not pass. – Cristiano Santos Dec 13 '11 at 12:21
You cannot have your cake and eat it too. This is by the way something that is quite essential in your question. You should update the question. Also I would not consider this a security risk but a functional constraint. – Pleun Dec 13 '11 at 12:24
As your answer are helpfull for some others, I will upvote it too =) – Cristiano Santos Dec 13 '11 at 12:40

score 0 · Answer 3 · answered Dec 13 '11 at 12:16

0

Have a look at cascade constraints clause

answered Dec 13 '11 at 12:16

zoran119

10,657
12
46
88

SQL DELETE from multiple tables

3 Answers3