CODEWITHSUNDEEP
mysqlsql
MAX POWER
MAX POWER

Reputation: 5458

MySQL - delete related table rows

Suppose I have the following (sample structure and data):

Table category
======================

id (PK)  name
-----------------
45       computers
50       sports


Table category_tag
======================

id (PK)  name         cat_id (FK)
---------------------------------
100      keyboard     45
120      football     50


Table listing_category
======================

list_id (PK)    cat_id (PK/FK)
------------------------------
10001           45
10001           50


Table listing_tag
======================

list_id (PK)    cat_tag_id (PK/FK)
----------------------------------
10001           100
10001           120

What needs to happen is, in the above example, when a row from the listing_category table is deleted, it needs to also delete the related entry(s) in table listing_tag.

Now I know I can combine table 'category' and table 'category_tag' but I prefer to keep these separate, as the category_tag table contains extra fields and data.

The other option is to add a cat_id column to table listing_tag - but I want to avoid duplicating data wherever possible.

Is it possible to create a "cascade delete" foreign key constraint using the above table configuration? If not, how should I change the tables for this to work?

Upvotes: 0

Views: 410

Answers (1)

Strawberry
Strawberry

Reputation: 33945

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT * FROM my_a;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  0 |   1.00 |   2.20 |
|  1 |   2.10 |   3.50 |
|  2 |   5.20 |   2.90 |
+----+--------+--------+

DELETE x
     , y 
  FROM ints x 
  JOIN my_a y 
    ON y.id = x.i 
 WHERE y.value1 = 1.00 
    AND y.value2 = 2.2;

SELECT * FROM my_a;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |   2.10 |   3.50 |
|  2 |   5.20 |   2.90 |
+----+--------+--------+

SELECT * FROM ints;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

Upvotes: 1

Related Questions