Magento 2 - can't add foreign key constraint with declarative schema (db_schema.xml) - Reference column X in reference table Y do not have index

Question

I try to add this table:

MODULE/etc/db_schema.xml:

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="company_configurator_configurations" resource="default" engine="innodb">
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Configuration ID"/>
        <column xsi:type="int" name="quote_item_id" padding="10" unsigned="true" nullable="false" comment="Quote Item ID"/>
        <column xsi:type="int" name="configurator_id" padding="10" unsigned="true" nullable="false" comment="Configuration ID"/>
        <column xsi:type="varchar" name="configurator_version" nullable="false" comment="Configuration Version"/>
        <column xsi:type="float" name="price" nullable="false" unsigned="true" comment="Price"/>
        <column xsi:type="int" name="qty" nullable="false" unsigned="true" comment="Qty"/>
        <column xsi:type="text" name="options" nullable="false" comment="Options JSON string"/>
        <column xsi:type="varchar" name="sku" nullable="false" comment="SKU"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>

        <constraint xsi:type="foreign"
            referenceId="COMPANY_CONFIGURATOR_CONFIGURATIONS_QUOTE_ITEM_ID_SALES_ORDER_ITEM_QUOTE_ITEM_ID"
            table="company_configurator_configurations"
            column="quote_item_id" 
            referenceTable="sales_order_item" 
            referenceColumn="quote_item_id" 
            onDelete="CASCADE"
        />
    </table>
</schema>

But my attempt to create the schema whitelist with this command:

$ bin/magento setup:db-declaration:generate-whitelist --module-name=Company_Configurator

... fails with this error:

Reference column quote_item_id in reference table sales_order_item do not have index

So I tried to add the index manualy:

... but it still fails with the same error.

---

This is my goal:

My new table company_configurator_configurations has the column quote_item_id.

I try to delete the record from company_configurator_configurations if the record in sales_order_item with the matching quote_item_id is deleted.

Answer 1

I solved it by adding the index to my db_schema.xml

...
<table name="sales_order_item" resource="default" engine="innodb">
    <!-- Needed for Foreign Key Constraint 'COMPANY_CONFIGURATOR_CONFIGURATIONS_QUOTE_ITEM_ID_SALES_ORDER_ITEM_QUOTE_ITEM_ID' -->
    <index referenceId="SALES_ORDER_ITEM_QUOTE_ITEM_ID" indexType="btree">
        <column name="quote_item_id"/>
    </index>
</table>
...