Missing Foreign Key / Cascade process for catalog_category_product_index table

[romainruaud]

Preconditions

1. Magento CE 2.1.7

Steps to reproduce

1. Create a category (let's call it "Test").
2. Assign products to this category.
3. Save it
4. Category/Product relations are properly figured in the catalog_category_product_index table.
5. Delete this Category

Expected result

1. Relations between products and this non-existing (deleted) category should be removed from catalog_category_product_index table

Actual result

1. Relations are still shown in the table.

Eg, just after I have deleted the category with id "48" :

mysql> select * from catalog_category_product_index where category_id=48;
+-------------+------------+----------+-----------+----------+------------+
| category_id | product_id | position | is_parent | store_id | visibility |
+-------------+------------+----------+-----------+----------+------------+
|          48 |       2050 |        0 |         1 |        1 |          4 |
|          48 |       2050 |        0 |         1 |        2 |          4 |
|          48 |       2051 |        0 |         1 |        1 |          4 |
|          48 |       2051 |        0 |         1 |        2 |          4 |
|          48 |       2052 |        0 |         1 |        1 |          4 |
|          48 |       2052 |        0 |         1 |        2 |          4 |
+-------------+------------+----------+-----------+----------+------------+

Actually there are no foreign key between "category_id" and the catalog_category_entity table (or the sequence_category table if using EE).

Maybe adding the foreign key could be worth it. Or just ensure that the reindex() function of the category will properly reindex the products affected to this category before the deletion.

[magento-engcom-team]

@romainruaud thank you for your bug report.
We've created internal ticket MAGETWO-75384 to track progress on the issue

[denysbabenko]

I'm working on this issue.

[sidolov]

Issue was fixed in 2.2-develop branch
Commit: e5f160a
Internal ticket: MAGETWO-82060