Missing unique key on eav_attribute_option_value table

[tzyganu]

This is similar to #24581 but for the table eav_attribute_option_value

Summary (*)

The table eav_attribute_option_value does not have a unique index on the column pair (option_id, store_id).
There is no reason for an option to have 2 values for the same store so this should be restricted.

Also layered navigation disappears on frontend.

Relates issues:
#24581
#24718

Examples (*)

Same as #24581, if by any faulty code an option gets 2 values for the same store view this causes a lot of issues when editing product / attributes / maybe others.

Proposed solution

add a unique key on the column pair (option_id, store_id) on the table eav_attribute_option_value

    <table name="eav_attribute_option_value">
       ......
        <constraint xsi:type="unique" referenceId="EAV_ATTRIBUTE_OPTION_VALUE_OPTION_ID_STORE_ID_UNIQUE">
            <column name="store_id"/>
            <column name="option_id"/>
        </constraint>
    </table>

[m2-assistant]

Hi @engcom-Charlie. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Add label Issue: Confirmed once verification is complete.

• 6. Make sure that automatic system confirms that report has been added to the backlog.

[magento-engcom-team]

✅ Confirmed by @engcom-Charlie
Thank you for verifying the issue. Based on the provided information internal tickets MC-20408 were created

Issue Available: @engcom-Charlie, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[magento-engcom-team]

Hi @tzyganu. Thank you for your report.
The issue has been fixed in #24720 by @UncleTioma in 2.3-develop branch
Related commit(s):

• 502146f7f2d086b3a1beaea41de3edc488f78b3c

The fix will be available with the upcoming 2.3.4 release.