Delete a row with relations with other tables

Question

If I have the following tables:

Product
+----+------+
| id | name |
+----+------+
|  1 | box  |
|  2 | car  |
|  3 | ball |
+----+------+

Color
+----+-------+
| id | name  |
+----+-------+
|  1 | red   |
|  2 | green |
|  3 | blue  |
+----+-------+

Size
+----+--------+
| id | number |
+----+--------+
|  1 |      1 |
|  2 |      5 |
|  3 |     10 |
+----+--------+

Color Options (#product | #color)
+---------+-------+
| product | color |
+---------+-------+
|       1 |     1 |
|       1 |     3 |
|       3 |     1 |
|       3 |     2 |
|       2 |     3 |
+---------+-------+

Size Options (#product | #size)
+---------+-------+
| product | color |
+---------+-------+
|       1 |     1 |
|       1 |     2 |
|       3 |     1 |
|       3 |     2 |
|       2 |     2 |
|       2 |     3 |
+---------+-------+

When I delete a product what is the best way to delete the relations it have with color and size? Do I need to do a delete in every table or there any automatic process?

i'd make a `deleteProduct($id)` kind of function that would run DELETE queries on all the tables — jimmy, May 02 '13 at 10:27

score 5 · Accepted Answer · edited May 23 '17 at 12:19

5

Have you set up proper RELATIONSHIP for your mysql? You can refer to this question How to create relationships in MySQL and MySQL foreign key constraints, cascade delete

edited May 23 '17 at 12:19

Community

1
1

answered May 02 '13 at 10:30

Willy Pt

1,795
12
20

1

you have to add the ON DELETE CASCADE when referencing the foreign key so that in your case, it will delete automatically – Willy Pt May 02 '13 at 12:15

score 1 · Answer 2 · answered May 02 '13 at 10:33

1

CREATE TABLE SIZE_OPTIONS(PRODUCT REFERENCES PRODUCT(ID), COLOR REFERENCES COLOR_OPTIONS(COLOR) ON DELETE CASCADE);

Use the same knind of the statement with COLOR_OPTIONS.

answered May 02 '13 at 10:33

Santhosh

1,771
1
15
25

every time I use a FOREIGN KEY should I use ON DELETE CASCADE? didn't you mean: CREATE TABLE SIZE_OPTIONS(PRODUCT REFERENCES PRODUCT(ID) ON DELETE CASCADE); – metRo_ May 02 '13 at 12:12
If you want relationship to have cascading effect, use ON DELETE CASCADE. Not, if you do not want that – Santhosh May 03 '13 at 13:52

score -1 · Answer 3 · answered May 02 '13 at 10:28

-1

INNER JOIN is an answer

DELETE
FROM Product p
  INNER JOIN Color_Options co
    ON co.product = p.id
  INNER JOIN Color c
    ON co.color = c.id
  INNER JOIN Size_Options so
    ON co.product = p.id
  INNER JOIN Size s
    ON so.color = s.id

answered May 02 '13 at 10:28

Muhammad Raheel

19,823
7
67
103

Delete a row with relations with other tables

3 Answers3

Linked