Home / Questions / 277105
Accepted
Fabian
Asked: 2020-07-06 12:45:46 +0800 CST 2020-07-06 12:45:46 +0800 CST

I need help with deleting data along with its related records in mysql

  • 772

I am trying to create a query to delete some data from a table called branch, but at the time of deleting it, I must also delete the relationships that exist exactly with that data.

i am doing something like this.

UPDATE empleado SET numSucursal = 0 WHERE numSucursal = 2;
DELETE FROM sucursal WHERE numSucursal = 2;

It works but I think it's wrong to do it this way.

the tables are these

CREATE TABLE IF NOT EXISTS sucursal(
    numSucursal int PRIMARY KEY NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    direccion VARCHAR(100) NOT NULL
);

and this

CREATE TABLE IF NOT EXISTS empleado(
    numEmpleado int PRIMARY KEY NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    sueldo bigint NOT NULL,
    fContratacion DATE NOT NULL,
    numSucursal int,
    jefe int NULL,
    CONSTRAINT fkjefe
    FOREIGN KEY (jefe) REFERENCES empleado(numEmpleado),
    CONSTRAINT fksucursal
    FOREIGN KEY (numSucursal) REFERENCES sucursal(numSucursal)
);

The error arises when I try to delete the row, it tells me ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

I haven't been able to find the solution.

I need your help, thank you very much.

mysql

2 Answers

  1. Best Answer

    This error gives you because the employees table with the foreign key of the branch prevents you from deleting a branch that has relationships with other tables, you need to delete the reference of the employees table and recreate it with an on delete cascade so that it allows you to delete but BEWARE you will lose all the records of the employee table associated with the branch. Here I leave you a small code but only execute it in case you want to delete the employees too, otherwise the way you do it is correct so as not to lose those records

    alter table empleado
drop constraint fksucursal;

alter table empleado
add constraint fksucursal
foreign key (numSucursal)
references sucursal(numSucursal)
on delete cascade;

    It should be noted that this restriction is for security to precisely avoid deleting unwanted data, it is always recommended to have this action disabled

    • 1

  2. You can avoid checking foreign key constraints

    SET FOREIGN_KEY_CHECKS=0;

    Now you perform the delete and you will see how it does not give you an error

    DELETE FROM sucursal WHERE numSucursal = 2;

    Then you activate the review of the restrictions again

    SET FOREIGN_KEY_CHECKS=1;
    • 0