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'.

probably you have specified `on delete cascade`. So this behavior. — Rahul, Mar 04 '14 at 16:35
here how can i create composite foreign key relationship(hook_id, hook_type) with article_id.( hook_type is article, tag) — Aruljothi, Mar 04 '14 at 16:40
I don't think you can do this with foreign key restraints. Seems like you're trying to store two different things in the same table. Maybe you should break it up into an article_hook table and tag_hook table. But, I'd still wonder why you'd want orphan records for the tags that don't exist. — xecaps12, Mar 04 '14 at 16:46

Rahul · Answer 1 · 2014-03-04T16:55:10.047

You probably have specified on delete cascade. So this behavior. You do have a foreign key relationship between the two mentioned tables.

According to the MySQL Foreign Key Constraints reference

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.

As per MySQL document here http://dev.mysql.com/doc/refman/5.5/en/example-foreign-keys.html

For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table.

So since both the tables persist with FK relationship; deleting a row from parent will also delete the matching row from child/referring table.

BASED ON YOUR COMMENT:

It is possible to create composite foreign key like below but for better performance try to decompose your table.

FOREIGN KEY (hook_id, hook_type) 
    REFERENCES article(article_id)

score 0 · Accepted Answer · answered Mar 04 '14 at 16:52

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'

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

2 Answers2