0

Hello can someone help me here.

I would like to delete from multiple tables.

I want to delete id_grupo = 6 from grupos. But when I delete it I would like to delete from gerir_posts where id_grupo = 6. But from posts, id_post = 4, that's the same id from gerir_posts id_post; I want delete it too.

So what i realy want is when i remove a grup , the posts that are in that grup most be deleted too.

Picture Tables

This is a example of my db.

   CREATE TABLE Utilizadores
(
    id_utilizador int auto_increment not null primary key,
    Nome varchar(50)
);

INSERT INTO utilizadores VALUES (1,'Admin');

CREATE TABLE Grupos
(
    id_grupo int auto_increment not null primary key,
    Nome_grupo varchar(50)
);
INSERT INTO grupos VALUES (1,'Grupo');

CREATE TABLE Gerir_grupos
(
    id_gerir_grupo int auto_increment not null primary key,
    id_grupo int,
    id_utilizador int,
    FOREIGN KEY (id_utilizador) references Utilizadores(id_utilizador),
    FOREIGN KEY (id_grupo) references Grupos(id_grupo) on delete cascade
);

INSERT INTO gerir_grupos VALUES (1,1,1);

CREATE TABLE Posts
(
    id_post int auto_increment not null primary key,
    id_utilizador int,
    Titulo_do_post varchar(50),
    Corpo_do_post varchar(500),
    FOREIGN KEY (id_utilizador) references Utilizadores (id_utilizador)
    
);
INSERT INTO posts VALUES (1,1,"teste","grupo teste");

CREATE TABLE Gerir_posts
(
    id_gerir_post int auto_increment not null primary key,
    id_post int,
    id_grupo int,
    FOREIGN KEY (id_post) references Posts (id_post) on delete cascade,
    FOREIGN KEY (id_grupo) references Grupos (id_grupo)on delete cascade
);

INSERT INTO gerir_posts VALUES (1,1,1);

If this can help

Picture Tables

Community
  • 1
  • 1
Hazonstorm
  • 1
  • 6
  • 3
    if i understand you, this is called a cascading delete. You can add triggers to do this or you can define the database to do them - depends on which RDBMS you are using for hte syntax – Randy Mar 30 '17 at 17:04
  • i just wanna delete whats in the red boxes when i select an id_grupo from the table grupos. – Hazonstorm Mar 30 '17 at 17:09

1 Answers1

3

You cannot specify many tables in a single delete statement.

Use a transaction

begin;
delete posts where post_id in (select post_id from gerir_posts where ...);
delete gerir_posts where ... ;    
delete grupos where ... ;
commit;

This way, everything is either deleted together, or stays intact.

Use ON DELETE CASCADE in your constraints

create table posts (
  post_id integer primary key,
  ...
);

create table gerir_posts (
  ...
  post_id integer;
  constraint gerir_posts_fk 
    foreign key(post_id) references(posts.post_id)
    on delete cascade,
);

Now if you delete a record from posts, all records in gerir_posts that refer to the same post_id are also deleted.

You can use alter table to add / modify constraints of the existing tables.

9000
  • 39,899
  • 9
  • 66
  • 104
  • These links will help you understand more: [1](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) [2](https://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/) [3](https://www.postgresql.org/docs/8.2/static/ddl-constraints.html#DDL-CONSTRAINTS-FK) [4](https://mariadb.com/kb/en/mariadb/foreign-keys/#examples). Copying someone's code without understanding is dangerous. – 9000 Mar 30 '17 at 17:58
  • You're one step from understanding how it works, and from having much easier time working with your database. Software developer's life is a constant study. – 9000 Mar 30 '17 at 18:06
  • its my final project and i would like to learn always more , im asking for help to understand better code, i have lots of problems in my project step by step i will finish it i hope – Hazonstorm Mar 30 '17 at 18:07
  • If I were in your shoes I'd [create two toy tables](http://sqlfiddle.com/#!9/c50f4), like described above, and played with them, using `insert`, `select`, and `delete`. After the "aha!" moment, when you see how deleting a record from one table also deletes a record from another table, where the foreign key points to the first table, I'd update my work tables with confidence. – 9000 Mar 30 '17 at 18:16
  • @user1827826: my contacts can be found in my profile. – 9000 Mar 30 '17 at 18:34
  • Hi ok i understand now better but i still have the same problem , with a simple delete on grupos table it deletes on 2 tables , but the problem i have is that posts does delete because its not a child of grupos – Hazonstorm Mar 30 '17 at 19:09
  • https://pastebin.com/4sYqRpEh what am i missing , the only table that stays with what i wanna remove is posts then – Hazonstorm Mar 30 '17 at 19:47
  • Do not use cascade delete without checking first with a competent dba. That is s very bad thing to use in many circumstances including teh case where you need to retain the record because there are child records (you would not delete a customer who had an order because then you would not be able to tell who the order was sent to.). It is very bad advice to point peopel to cascade delete as a practice. Only people with at least ten years of database experience have any business using that feature. Do the deletes in the transaction. – HLGEM Mar 30 '17 at 20:30
  • @HLGEM: This being a student project, I think it's OK to use cascade deletes for the sake of education. A reasonable production database will have a wad of design documents, an approval process, etc, so an inexperienced developer won't ruin it this way. (Disclaimer: I worked as a DBA, too.) – 9000 Mar 30 '17 at 20:51
  • what i want to do is when i remove a selected group i wanna delete the posts in that group. My problem is that table Posts is a parent and i cant cascade it – Hazonstorm Mar 31 '17 at 10:19
  • @Hazonstorm: I got your link; the problem is trickier since you have the dependencies between tables in the reverse direction. I'll update the answer when I have time likely later today. – 9000 Mar 31 '17 at 14:51
  • @Hazonstorm: Sorry, not yet. – 9000 Apr 03 '17 at 17:52