Cascade delete in MySQL with DELETE

Question

Cascade delete in MySQL with DELETE

Navigation

#1 by (3 votes)
#2 by (2 votes)

1

I have 3 tables in my MySQL database.

Clientes (existe el DNI)
Clientes_compra (puede existir el DNI o quizás no).
Clientes_alquiler (puede existir el DNI o quizás no).

My intention is to ask for a Java program the textfield_DNI and if it matches a DNI of the table clientes that erases it from this table and the other two, if it exists.

DELETE FROM clientes WHERE dni="13225217P";
DELETE FROM clientes_compra WHERE dni="13225217P";
DELETE FROM clientes_alquiler WHERE dni="13225217P";

I think there is a cascade deletion ( CASCADE ON ), how would it be in a single query and not have to use 3?

Execution of error:

Cannot delete or update a parent row: a foreign key constraint fails ('osmarentcars'.'clientes_alquiler', CONSTRAINT 'FK_CLIENTES_ALQUILER1' FOREIGN KEY ('DNI') REFERENCES 'clientes' ('DNI'))

sql

asked by omaza1990 19.01.2017 в 22:46

source

2 answers

2

I think this is what you are looking for

DELETE c, cc, ca 
FROM clientes c 
LEFT JOIN clientes_compra cc 
    ON c.dni = cc.dni 
LEFT JOIN clientes_alquiler ca 
    ON ca.dni = c.dni 
WHERE c.dni = "13225217P";

answered by 19.01.2017 в 23:04

What is the difference between posting to file systems and web deploy on asp.net? Drag and drop, have all the elements on one side or the other

score 3 · Accepted Answer

If you want to use a CONSTRAINT , what you have to do is declare a FOREIGN KEY , a link between the dni of the tables. In MySql you see, for example, in CREATE TABLE of customers_buys as:

 FOREIGN KEY (dni) REFERENCES clientes (dni) ON DELETE CASCADE

NO I recommend it. That means that in the case that you delete a customer, all purchases with the same value of dni are automatically deleted, without error, without asking.

In the ideal case, you declare a transaction, and you make 3 queries, first deleting the rows in the dependent tables and finally entering customers.

There is no logical reason to get loose with a couple of lines of code, sacrificing a good concept of data security in the process.

If you insist for some reason to do so with a query, what should work is (if I remember correctly the sequence in which the BD erases)

DELETE ca, cc, c 
FROM clientes_alquilier ca 
LEFT JOIN clientes_compra cc 
    ON ca.dni = cc.dni 
LEFT JOIN clientes c 
    ON c.dni = cc.dni 
WHERE c.dni = "13225217P";