CODEWITHSUNDEEP
mysqlsqlleft-joinjoin
Cristiano Santos
Cristiano Santos

Reputation: 2137

SQL DELETE from multiple tables

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.

Upvotes: 3

Views: 1107

Answers (3)

Pleun
Pleun

Reputation: 8920

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)

Upvotes: 1

jenson-button-event
jenson-button-event

Reputation: 18961

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

Upvotes: 3

zoran119
zoran119

Reputation: 11327

Have a look at cascade constraints clause

Upvotes: 0

Related Questions