0

I have written one custom module with a simple UpgradeSchema file to drop core foreign key (table product_alert_stock, foreign key name - PRODUCT_ALERT_STOCK_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID )

ReferenceLink

public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
    $setup->startSetup();

    $setup->getConnection()->dropForeignKey('product_alert_stock',
         'PRODUCT_ALERT_STOCK_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID'
    );

    $setup->endSetup();
}

And it is working very fine ( as required dropping that foreign key constraint) as I run setup:upgrade command first time to install this new custom module but when the next time I run setup:upgrade for any other purpose, that foreign key added back to the table with same name and not being dropped again, no matter how many time I run setup upgrade.

Additionally as I checked if I delete my custom module name entry from setup_module table and run setup upgarde it is dropping that foreign key and on next setup upgrade adding back again. I am guessing that as it is magento's core foreign key so somehow in upgrade, magento is adding it back but as I have written UpgradeSchema without checking the module version it should run and work every time on setup upgrade. So I am not understanding why it is working only first time on fresh module install. I really need to drop this key permanently.

If anyone know or have any other way to drop this foreign key, please help me out here. I already tried google for any solution but didn't find anything. Please suggest what changes or approach should I make to get this done

Thanks in advance.

2 Answers 2

0

Well, first thing first - you are using upgrade schema approach (which is deprecated) and you should instead use the declarative schema or data patches approach as the upgrade scripts will no longer be supported in the future.

If you check the https://devdocs.magento.com/guides/v2.4/extension-dev-guide/declarative-schema/db-schema.html#drop-a-foreign-key, you will see that in order to drop a foreign key, you have to also generate the schema whitelist file which provides a history of all tables, columns, and keys added with declarative schema. It is required to allow drop operations.

2
  • 1
    You're awesome. Thanks you so much, you just made my day. That is the real approach I was looking for. May 19, 2021 at 17:39
  • you are most welcome! :)
    – Diana
    May 20, 2021 at 6:36
1

As Diana suggested that the correct approach to drop foreign key from table is to use declarative schema. So I used below db_schema.xml in my module to remove that foreign key -

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
  <table name="product_alert_stock">
   <constraint xsi:type="foreign" referenceId="PRODUCT_ALERT_STOCK_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID " table="product_alert_stock" column="customer_id" referenceTable="customer_entity" referenceColumn="entity_id" onDelete="CASCADE" disabled ="true"/>
  </table>
</schema> 

And it worked . !! Thanks Diana Botean

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.