magento2: MIssing unique key on eav_attribute_label table

Summary (*)

Magento 2.4-develop

The table eav_attribute_label should contain a unique key constraint on the (store_id, attribute_id) pair.
Version:

• Identified on 2.3.2 Magento Open Source
• Checked table structure for version 2.2.6, key is still missing.
• Didn’t check versions prior to 2.2.6, but most probably it has been there since the beginning of time.

Also layered navigation disappears on frontend.

Relates issues: https://github.com/magento/magento2/issues/24581 https://github.com/magento/magento2/issues/24718

Steps to reproduce:

1. Create a product with any attribute;
2. Open eav_attribute_label and add a new row with the same attribute_id and store_id values

1. Go to Storefront -> Product page

Actual Result: ✖️ Item with the same id already exists.

Expected Result: ✔️ should be notified that duplicate values cannot be created for these fields

Examples (*)

One (faulty?) 3rd party extension inserted something in the eav_attribute_label using insertOnDuplicate, but since there is no unique key on the table I ended up with duplicate pairs (store_id, attribute_id). This resulted in “item with id X already exists” error when loading attribute labels

Proposed solution

Add a unique key constraint on the (store_id, attribute_id) pair.

        <constraint xsi:type="unique" referenceId="EAV_ATTRIBUTE_LABEL_ATTRIBUTE_ID_STORE_ID_UNIQUE">
            <column name="store_id"/>
            <column name="attribute_id"/>
        </constraint>

as a bonus, since this is becoming a unique index, the existing index on the store_id, attribute_id pair can be dropped

        <index referenceId="EAV_ATTRIBUTE_LABEL_ATTRIBUTE_ID_STORE_ID" indexType="btree">
            <column name="attribute_id"/>
            <column name="store_id"/>
        </index>