Stored procedure to delete data in referenced tables

Question

Stored procedure to delete data in referenced tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I need help with a stored procedure that allows me to delete a column in the usuarios table and when this column is deleted, delete all the data where it is used as primary key its id , the table that inherits from usuarios is facturas and this invoice has a detail invoice det_factura inheriting the primary key from facturas .

GO
CREATE PROCEDURE sp_eliminarusuario 
@iduser int
AS
BEGIN
DELETE det_facturas FROM facturas f, det_facturas d, usuarios u WHERE 
f.idfactura = d.idfactura and f.idusuario=u.idusuario
DELETE facturas FROM facturas f, usuarios u where f.idusuario=u.idusuario
DELETE FROM usuarios WHERE idusuario = @idusers
END

This procedure removes all the data in facturas as in det_facturas as it could solve it

mysql sql sql-server

asked by Javtronic 07.02.2018 в 19:59

source

2 answers

Help with a list type object or modify code to make it work How to add several records at once with php and javascript

score 1 · Answer 1

Not very well tending the structure that you mention from the database, It would be interesting to add a mini ERM diagram of your BDD with its relations of each table.

Although if you are using MySql, with the Engine = InnoDB and what you are trying to eliminate are associated data from an FK (Foreign Key) you should configure the 'ON CASCADE' option in each FK relationship in this case it would be:

ON DELETE CASCADE

Example:

CREATE TABLE user(
 id INT NOT NULL,
 PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
  id INT,
  parent_id INT,
  FOREIGN KEY (parent_id)
    REFERENCES parent(id)
    ON DELETE CASCADE
) ENGINE=INNODB;

I leave you the official documentation of Mysql about 'ON CASCADE': Here

score 0 · Answer 2

It would be interesting to know if the foreign keys that refer to the users table have the cascade option. If so, you simply delete the user table and automatically delete all records from the other tables that refer to it.

Otherwise you could do it by means of subquery, for example:

DELETE FROM det_facturas WHERE idfactura = (
SELECT id FROM facturas INNER JOIN usuarios ON usuarios.id = factura.idusuario);

DELETE FROM facturas WHERE idusuario = (SELECT id usuario WHERE id = ?);

DELETE FROM usuarios WHERE id = ?

Depending on the need, you can add it to a function or procedure.