1

We created a new table using M2's declarative scheme like this:

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="company_quote_item_original_sku" engine="innodb" resource="default" comment="">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment=""/>
        <column xsi:type="int" name="quote_item_id" padding="10" unsigned="true" nullable="false" comment=""/>
        <column xsi:type="varchar" name="original_sku" nullable="true" length="255" comment=""/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="FK_QUOTE_ITEM_ID" table="company_quote_item_original_sku"
            column="quote_item_id" referenceTable="quote_item" referenceColumn="item_id"
            onDelete="CASCADE"/>
    </table>
</schema>

This worked great. Then we tried to add another table by updating the schema, like so:

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="company_quote_item_original_sku" engine="innodb" resource="default" comment="">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment=""/>
        <column xsi:type="int" name="quote_item_id" padding="10" unsigned="true" nullable="false" comment=""/>
        <column xsi:type="varchar" name="original_sku" nullable="true" length="255" comment=""/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="FK_QUOTE_ITEM_ID" table="company_quote_item_original_sku"
            column="quote_item_id" referenceTable="quote_item" referenceColumn="item_id"
            onDelete="CASCADE"/>
    </table>

    <table name="company_order_item_original_sku" engine="innodb" resource="default" comment="">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment=""/>
        <column xsi:type="int" name="order_item_id" padding="10" unsigned="true" nullable="false" comment=""/>
        <column xsi:type="varchar" name="original_sku" nullable="true" length="255" comment=""/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="FK_ORDER_ITEM_ID" table="company_order_item_original_sku"
            column="order_item_id" referenceTable="sales_order_item" referenceColumn="item_id"
            onDelete="CASCADE"/>
    </table>
</schema>

This triggered the following error:

General error: 1826 Duplicate FOREIGN KEY constraint name 'magento2/COMPANY_QUOTE_ITEM_ORIGINAL_SKU_QUOTE_ITEM_ID_QUOTE_ITEM_ITEM_ID'

ALTER TABLE `company_quote_item_original_sku` MODIFY COLUMN `entity_id` int(10) UNSIGNED NOT NULL  AUTO_IN
CREMENT COMMENT "", MODIFY COLUMN `quote_item_id` int(10) UNSIGNED NOT NULL   COMMENT "", MODIFY COLUMN `original_sku` varchar(255) NULL  COMMENT "", ADD CONSTRAINT `COMPANY_QUOTE_ITEM_ORIGINAL_SKU_QUOTE_ITEM_ID_QUOTE_ITEM_ITEM_ID` FOREIGN KEY (`quote_item_id`) REFER
ENCES `quote_item` (`item_id`)  ON DELETE CASCADE, COMMENT=''

It looks like Magento is incorrectly diffing my existing table and the new schema-generated table. It should do a REMOVE before it does the ADD on the constraint, but it doesn't.

Has anyone seen this before?

  • 1
    Have you generated schema whitelist before adding new table? – Rutvee Sojitra May 27 '19 at 6:30
0

From the DevDocs:

Backward compatibility must be maintained. Therefore, declarative schema does not automatically delete database tables, columns or keys that are not defined in a db_schema.xml file.

You must create a db_schema_whitelist.json for your module. In declarative schema a destructive operation is only permitted if allowed in the whitelist.

You can generate the whitelist for your module with the following command:

bin/magento setup:db-declaration:generate-whitelist --module-name[=MODULE-NAME]
| improve this answer | |

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.