Fixing Broken Product Relationships in Adobe Commerce/Magento 2

I recently ran into a fairly puzzling issue on an Adobe Commerce site where some products were behaving oddly on the frontend. After some digging, it turned out the catalog_product_relation and catalog_product_super_link tables had invalid entries - simple products were listed as parents with child relationships, which should never happen.

In Magento/Adobe Commerce, only configurable, bundle and grouped products should have child relationships. When simple products end up with children in these tables, it’s usually the result of a botched import, a failed product type conversion or some other data corruption.

¶Finding the problem

First, identify any invalid entries in catalog_product_relation where the parent is a simple product:

SELECT
    cpr.parent_id,
    cpr.child_id,
    parent.entity_id as parent_entity_id,
    parent.sku as parent_sku,
    parent.type_id as parent_type,
    child.entity_id as child_entity_id,
    child.sku as child_sku,
    child.type_id as child_type
FROM
    catalog_product_relation cpr
INNER JOIN
    catalog_product_entity parent ON cpr.parent_id = parent.row_id
INNER JOIN
    catalog_product_entity child ON cpr.child_id = child.row_id
WHERE
    parent.type_id = 'simple'
ORDER BY
    parent.entity_id, child.entity_id;

Similarly, check catalog_product_super_link for non-configurable parents (this table should only contain configurable product relationships):

SELECT
    cpsl.link_id,
    cpsl.product_id,
    cpsl.parent_id,
    parent.entity_id as parent_entity_id,
    parent.sku as parent_sku,
    parent.type_id as parent_type,
    child.entity_id as child_entity_id,
    child.sku as child_sku,
    child.type_id as child_type
FROM
    catalog_product_super_link cpsl
INNER JOIN
    catalog_product_entity parent ON cpsl.parent_id = parent.row_id
INNER JOIN
    catalog_product_entity child ON cpsl.product_id = child.row_id
WHERE
    parent.type_id != 'configurable'
ORDER BY
    parent.entity_id, child.entity_id;

You can also get a quick count of how many invalid entries exist:

SELECT 'catalog_product_relation' as table_name,
       COUNT(*) as invalid_count,
       'Parents with type_id=simple' as issue
FROM catalog_product_relation cpr
INNER JOIN catalog_product_entity parent ON cpr.parent_id = parent.row_id
WHERE parent.type_id = 'simple'

UNION ALL

SELECT 'catalog_product_super_link' as table_name,
       COUNT(*) as invalid_count,
       'Parents with type_id!=configurable' as issue
FROM catalog_product_super_link cpsl
INNER JOIN catalog_product_entity parent ON cpsl.parent_id = parent.row_id
WHERE parent.type_id != 'configurable';

¶Checking for orphans

While you’re at it, check for orphaned references where the parent or child product no longer exists:

-- Missing parents in catalog_product_relation
SELECT cpr.parent_id, cpr.child_id, 'Missing parent' as issue
FROM catalog_product_relation cpr
LEFT JOIN catalog_product_entity parent ON cpr.parent_id = parent.row_id
WHERE parent.row_id IS NULL;

-- Missing children in catalog_product_relation
SELECT cpr.parent_id, cpr.child_id, 'Missing child' as issue
FROM catalog_product_relation cpr
LEFT JOIN catalog_product_entity child ON cpr.child_id = child.row_id
WHERE child.row_id IS NULL;

¶Cleaning up

Back up your database first. Then remove the invalid entries:

-- Remove invalid entries from catalog_product_relation
DELETE cpr
FROM catalog_product_relation cpr
INNER JOIN catalog_product_entity parent ON cpr.parent_id = parent.row_id
WHERE parent.type_id = 'simple';

-- Remove invalid entries from catalog_product_super_link
DELETE cpsl
FROM catalog_product_super_link cpsl
INNER JOIN catalog_product_entity parent ON cpsl.parent_id = parent.row_id
WHERE parent.type_id != 'configurable';

After cleanup, reindex and clear cache:

bin/magento indexer:reindex
bin/magento cache:clean

One thing to consider - if those simple products should actually be configurable products, you’ll need to convert them rather than just deleting the relationships. The queries above assume the relationships themselves are the problem, not the product types.