Delete a record having related tables

Question

Delete a record having related tables

Navigation

#1 by (2 votes)

-1

I need to delete the idcliente , nombre and apellido of the table cliente , but it will not let me delete it because it is related to the ubicacion table. For that, I also want to eliminate all records of that client id in all the tables.

This is the structure of the tables:

create table cliente(
    idcliente    varchar(50) primary key,
    nombre       varchar(50),
    apellido     varchar(50)
)

create table ubicacion(
    idubicacion  varchar(50) primary key,
    pais         varchar(50),
    ciudad       varchar(50),
    fkcliente    varchar(50)
)

create table venta(
    idproducto   varchar(50),
    producto     varchar(50),
    fkubicacion  varchar(50)
)

alter table ubicacion 
  add constraint fkcliente_idcliente
  foreign key (fkcliente) references cliente(idcliente)

alter table venta
  add constraint fkubicacion_idubicacion
  foreign key (fkubicacion) references ubicacion(idubicacion)

sql sql-server

asked by Sneider Muñoz 01.12.2017 в 15:36

source

1 answer

Can you convert Excel tables to a mysql database? [closed] Problem with exception laravel 5.5

score 2 · Accepted Answer

To be able to achieve this you must go erasing in order (unless you decide that your foreign keys are in cascade). First you must delete the corresponding rows of the table venta , then the table ubicacion and finally the table cliente :

DECLARE @idcliente varchar(50);
SET @idcliente = 'algún idcliente válido';

DELETE v
FROM dbo.venta v
INNER JOIN dbo.ubicacion u 
    ON v.fkubicacion = u.idubicacion
INNER JOIN dbo.cliente c
    ON u.fkcliente = c.idcliente
WHERE c.idcliente = @idcliente
;

DELETE u
FROM dbo.ubicacion u 
INNER JOIN dbo.cliente c
    ON u.fkcliente = c.idcliente
WHERE c.idcliente = @idcliente
;

DELETE FROM dbo.cliente
WHERE idcliente = @idcliente
;