PHP, MySQL - delete from multiple tables with join

Question

I have 3 tables: categories, posts and comments.

categories and posts are connected via categories.id_category = posts.category_id and posts and comments are connected via posts.id_post = comments.post_id.

When I delete category, I want also to delete all the posts related to that category and all comments related to that posts. Can this be done in one query?

The best way would be to create your tables with foreign keys and use references in tables -> called referential integrity. Take a look at dev.mysql.com/tech-resources/articles/… Maybe this is the right thing for you — thedom
– thedom, Commented Jul 8, 2014 at 11:36
And if you do not want foreign key and want to do other way use trigger to handle it. — Abhik Chakraborty
– Abhik Chakraborty, Commented Jul 8, 2014 at 11:43

enridaga · Accepted Answer · 2014-07-08 11:51:56Z

3

MySQL InnoDB databases support the FOREIGN KEY constraint with ON DELETE CASCADE clause. With this specifications, related dependent table rows will be deleted when doing a DELETE on the referred table.

This is an example:

CREATE TABLE categories (
    id_category int unsigned not null primary key,
    ...
);
CREATE TABLE posts (
    id_post int unsigned not null primary key,
    category_id int unsigned not null,
    ...
    FOREIGN KEY (category_id) REFERENCES categories (id_category)
       ON DELETE CASCADE
);
CREATE TABLE comments (
    post_id int unsigned not null,
    ...
    FOREIGN KEY (post_id) REFERENCES posts (id_post)
       ON DELETE CASCADE
);

Here you can find documentation about the use of foreign keys in MySQL: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

edited Jul 8, 2014 at 11:51

answered Jul 8, 2014 at 11:43

enridaga

6414 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sasha Over a year ago

So, the logic is that I only need to delete category and all the posts and comments will be auto deleted?

enridaga Over a year ago

Yes, you can do a single DELETE on the categories table and all referenced rows will be deleted too. I clarified this in the answer.

Sasha Over a year ago

Cool :). Can I alter my tables or I need to create new ones in order for the foreign key to work?

Giulio De Marco Over a year ago

You can alter tables, for example: ALTER TABLE posts ADD INDEX (category_id); to add the index, then ALTER TABLE posts ADD FOREIGN KEY (category_id) REFERENCES categories ( id ) ON DELETE CASCADE ON UPDATE NO ACTION ;

Collectives™ on Stack Overflow

PHP, MySQL - delete from multiple tables with join

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related