mysql query delete multiple tables with single query when the foreign key relationship

Question

I have create blog system. In this blog i have created three tables.

Table 1: article

+----------------+-----------+-------+-------------+
| article_id(PK) | name      | description         |
+----------------+-----------+-------+-------------+
| 1              | article-1 | lorem ipsum       |
| 2              | article-2 | lorem ipsum       |
+----------------+-----------+-------+-------------+

Table 2: article_hook

+---------------------------+---------+-----------+
| article_hook_id(PK, auto) | hook_id | hook_type |
+---------------------------+---------+-----------+
| 1                         | 1       | article   |
| 2                         | 1       | tag       |
+---------------------------+---------+-----------+

Here I have created foreign key relationship. and i want delete the article create delete query

DELETE FROM article WHERE article_id = 1 LIMIT 1

It's deleted the record successfully. But it delete all the records in article_hook table where hook_id = 1. But I need to delete only one record from article_hook table when I pass Hook_id=1 and hook_type='article'.

Answer 1

If you can't break the tables apart as I mentioned in my comment, you could do it via a delete trigger instead.

CREATE TRIGGER delete_hooks AFTER DELETE on article
DELETE from article_hook 
    WHERE article_hook.hook_id = old.id 
        AND hook_type = 'article'