Delete data in two tables with one SQL statement

Question

Hi Stackoverflow users,

I have a problem with deleting data in two tables with their foreign keys.

Intro: I created a blogging website hosted locally with xampp and written in PHP and MySQL. Basically I have the functionality of it done, but that's not why I'm here. I have a problem with deleting data from two tables at once.

Scenario:

The structure of part of my database is shown in the picture below

Let's say a user created a post for his website, the post is stored in table blog_entries, username is referenced from user_table and a unique entry_id is generated for that post.

I created a separate table for storing comments added to a particular post called entry_comments. With entry comments, i also reference username from user_table so that people can tell who made the comment and referenced entry_id from blog_entries for that post.

The problem is when the user goes to their profile in order to delete a post, I want the database to delete all the comments associated with that post before deleting the post itself.

My Code

I achieved this by deleting the comments for a post first before deleting the post itself, but I want to know if there is a way to delete data from both tables by using a single query as shown below.

$entry_id = mysqli_real_escape_string($DBConn, $_GET['entry_id']);

        $query1 = "DELETE FROM $entry_comments WHERE entry_id='$entry_id'";
        $result1 = mysqli_query($DBConn, $query1);
        if(!$result1){
            //display error message if query failed
        }
        else{
         $_GLOBAL['message'] = "Comments with id $entry_id has been deleted!!!";
         }
        $query2 = "DELETE FROM $blog_entries WHERE entry_id='$entry_id'";
        $result = mysqli_query($DBConn, $query2);
        if(!$result){
            //display error message if query failed
        }
        else{ $_GLOBAL['message'] .= "Entry with id $entry_id has been deleted!!!";
        include 'profile.php'; //redirect
        }

So i tried shortening the query to use a Join query in order to delete data from two rows with this query but it didn't work.

DELETE FROM $blog_entries, $entry_comments 
        WHERE $blog_entries.username =  $entry_comments.username
        AND entry_id='$entry_id'

or I used

DELETE be, ec
  FROM $blog_entries be
  JOIN $entry_comments ec ON be.username = ec.username
 WHERE be.entry_id = '$entry_id'

I researched the problem and the closest answer I found to my problem was this: How to delete from multiple tables in MySQL? and I tried to rewrite my sql code to match it but would still get an error saying it has problems deleting due to foreign keys, and I can't figure out what I'm doing wrong.

451 - Cannot delete or update a parent row: a foreign key constraint fails (`thenefariousblog`.`entry_comments`, CONSTRAINT `entry_comments_ibfk_2` FOREIGN KEY (`entry_id`) REFERENCES `blog_entries` (`entry_id`))

Sorry if this post feels a little bit long-winded, but i'm trying to explain the problem I have as much as possible in order to get the best feedback

You generally can't delete from two different tables in a single statement in any flavor of SQL. Really, cascading delete is the only type of delete which should automatically happen across multiple tables. — Tim Biegeleisen, Aug 20 '18 at 14:40
"You generally can't delete from two different tables in a single statement in any flavor of SQL" Doesn't MySQL support `DELETE table1, table2 ...` to delete from both tables ? @TimBiegeleisen it seams to be working https://www.db-fiddle.com/f/sZMTVB3oL6jvxAzEmaZowE/0 .. It's also in the manual https://dev.mysql.com/doc/refman/8.0/en/delete.html section "**Multiple-Table Syntax**" — Raymond Nijland, Aug 20 '18 at 14:53
@RiggsFolly I modified my code like the accepted answer on that answer, and it's no different to what I originally put as one of the codes I've tried already. I modified the code to suit the answer you provided, code runs, but nothing is deleted — Kebab Programmer, Aug 20 '18 at 14:57
@RaymondNijland. Yes, MySQL may allow this under certain conditions, but I think it is intended mainly for MyISAM, which does not support cascading deletes. On InnoDB, I don't think it should really be used. — Tim Biegeleisen, Aug 20 '18 at 14:57
@TimBiegeleisen, RaymondNijland I thought I was under the impression that I couldn't delete the comments because of the 2 foreign keys I have in the entry_comments table, but that's not right because the 2 step method I have deletes the comments for me, so I'm kind of lost :( — Kebab Programmer, Aug 20 '18 at 14:59
@ProgrammingNewb MySQL won't let you delete a parent row whose primary key is still being referred to by children records, through their foreign keys. My guess is that this is the general cause of your problem. The discussion about delete joins is something else, not really your OP. — Tim Biegeleisen, Aug 20 '18 at 15:01
And I'm aware that MySQL won't let me delete parent rows when children are still calling them, hence, the 2 step approach, I was under the impression that there would be a way i'd run a delete query that removes data from the child record first, then the parent. — Kebab Programmer, Aug 20 '18 at 15:07
@ProgrammingNewb "there would be a way i'd run a delete query that removes data from the child record first, then the parent" -- That's the Cascading Delete that Tim mentioned. It's part of the FK declaration. — Kevin Bott, Aug 20 '18 at 15:09
@KevinBott I researched cascade deleting, and it does seem to be the solution to my problem. Now I'm using phpmyadmin as my database, will I go in to the tables, and set the keys for the blog_entries so that anytime a record is deleted from this table, all the data linked to it in the entry_comments table will be deleted? — Kebab Programmer, Aug 20 '18 at 15:15
I found this arcticle, https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php, I think it might work, so I'll carry on for now, thanks y'all for pointing me in the right direction — Kebab Programmer, Aug 20 '18 at 15:17

score 0 · Answer 1 · answered Aug 20 '18 at 14:47

You can create the procedure to delete data in a proper order as you did in your 2-step approach. The input parameter for procedure would be the post ID. In this way you could simply run: call removeEntry(ID);

You can also use ON DELETE CASCADE clause.

You can also try to temporarily disable the foreign key check like here: How to temporarily disable a foreign key constraint in MySQL?

Delete data in two tables with one SQL statement

1 Answers1