Delete a records and records related to this, MySql

Question

Delete a records and records related to this, MySql

Navigation

#1 by (1 votes)

2

know how I can delete a record and at the same time all records related to this, I currently have the table tb_artista in which I want to delete a record of that table with the id AR1, at the same time in tb_album I want to delete a table with id AL1 that has the id AR1 as a key forarena, and at the same time I want to delete a record of the music table that has as a foreign key the id AL1, not if I let myself explain here is my mysql script:

create database ProyectoMusica;
use ProyectoMusica;


create table tb_artista(
idartista varchar(200) primary key,
nombreartista varchar(90),
anonacimiento varchar(90),
descripcion1 text,
descripcion2 text,
fotoartista varchar(90)
);

insert into tb_artista values('AR1','Adam Gointer','18/02/1988','Nose','Nose','AdamGointer');
insert into tb_artista values('AR3','E Gointer','18/02/1988','Nose','Nose', 'EGointer');



create table tb_album(
idalbum varchar(200) primary key,
idartista varchar(200),
nombrealbum varchar(200),
tipo varchar(20),
fotoalbum varchar(100),
fechacreacion varchar(20),
FOREIGN KEY (idartista) REFERENCES tb_artista(idartista)
);

insert into tb_album values('AL1','AR1','Dulce Agonia','FREE','DulceAgonia', CURDATE());

create table tb_musica (
idmusica varchar(200) primary key,
idalbum varchar(200),
nombremusica varchar(90),
tamaño decimal(8,2),
FOREIGN KEY (idalbum) REFERENCES tb_album(idalbum)
);

I update Try this way but it does not work out:

 delete tal,tar,tm from  tb_artista as tar inner join  tb_album as tal
 on tar.idartista=tal.idartista inner join tb_musica as tm on tal.idalbum = tm.idalbum
 where tar.idartista = 'AR1'

mysql

asked by Alex 04.12.2018 в 15:02

source

1 answer

java.lang.NullPointerException in a RecyclerView Validate Onclick event in JavaScript

score 1 · Accepted Answer

You need to establish the relationships of your foreign keys with:

ON DELETE CASCADE

and also

ON UPDATE CASCADE

So when you delete a parent record, the associated child record is also deleted

If you are just creating your database, your table should have a statement similar to this

CONSTRAINT fk_album_artista FOREIGN KEY(idartista) 
REFERENCES tb_artista(idartista) ON DELETE CASCADE ON UPDATE CASCADE

REMEMBER THAT DEPENDING ON WHAT YOU NEED TO DO, YOU HAVE:

On delete cascade: Deleting the record associated with the primary key

On delete restrict: Prevents carrying out said action

On delete SET NULL: Sets to NULL the values associated with the primary key that is being deleted

On delete no action: in MYsql, putting NO ACTION has the same effect as putting RESTRICT that is, preventing that action from taking   out