PHP，MySQL - 使用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?

Answer 1

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