I am learning to use MySQL and I need help creating a PA to erase fields of 2 tables at a time, this is what I tried and it did not work
CREATE PROCEDURE BorradoCascada
BEGIN
DECLARE
@IDalumno INT();
delete FROM alumno where IDalumno = @IDalumno;
DELETE FROM matricula WHERE IDalumno = @IDalumno;
END
I see at least 3 problems that need to be corrected:
@IDalumno , but in reality, your intention should be to define it as a parameter of your procedure. DELIMITER before and after creating your procedure to prevent MySQL from interpreting the semicolons as the end of the CREATE PROCEDURE statement. DELETE to alumno before matricula . If you have defined a foreign key between the 2 tables, which would be correct, then the DELETE to alumno will fail because there would still be records in matricula that point to alumno . Logically, you must delete the child records before the father. Corrected version:
delimiter $$
CREATE PROCEDURE BorradoCascada(pIDalumno INT)
BEGIN
DELETE FROM matricula WHERE IDalumno = pIDalumno;
DELETE FROM alumno WHERE IDalumno = pIDalumno;
END$$
delimiter ;
You can run it like this:
CALL BorradoCascada(10);
Alternatively, you could choose to define (or redifine if it already exists) a foreign key between the 2 tables that automatically deletes the corresponding records in matricula when you delete a record in the alumno table. This would prevent you from having to define this procedure. You would simply make DELETE with table alumno , and this would be responsible for deleting records in matricula automatically.
You can achieve this with a FOREIGN KEY .. ON DELETE CASCADE . Example:
alter table matricula
add constraint matricula_fk
foreign key (IDalumno)
references alumno (IDalumno)
on delete cascade;
Then, instead of executing a procedure, you would simply execute the following DELETE :
DELETE FROM alumno WHERE IDalumno = 10;