0

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:

enter image description here

... 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.

3
  • Shouldn't the better solution be (1) if you going to use sales_order_item table then use the item_id as reference key or (2) if you want to use quote_item_id then you use the quote_item table wiht item_id as the reference key? Aug 2 at 13:54
  • Yes, but this would require me to rewrite the code of the extension and I try to avoid it.
    – Black
    Aug 2 at 14:01
  • 1
    Adding a key to a core table will cause a lot more issues in the long run Aug 2 at 14:19

1 Answer 1

0

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>
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.