Magento 2 foreign key constraint error

Question

I've got this error when run "bin/magento setup:upgrade".

Module 'Magento_Catalog':
Running schema recurring...

  [PDOException]                                                                                        
  SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'mydb/CAT_CTGR_PRD_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID'

What can cause it?

Answer 1

Run command:

php bin/magento setup:db-declaration:generate-whitelist

Answer 2

just run below command if your magento version Magento 2.3 or Magento 2.4

php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module

above is for generate whitelist for perticular module or run below for all modules

run command: php bin/magento setup:db-declaration:generate-whitelist

hope this helps you :)

Answer 3

I had this same problem on a custom table. After creation I needed to add new columns to this table.

To make such a change to the table in declarative mode I needed to generate the db_schema_whitelist.json.

Run the following command:

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

You can learn more about declarative schema at: Configure declarative schema

Answer 4

Same issue there, it seems that my columns declaration don't match (between the reference table and the table owning the foreign key), for example: one is unsigned and not the other, or one is nullable or not the other.

I didn't find a way to solve it yet, except by making these columns match (which is not what I want, for example to have a nullable foreign key).

What I don't understand is that Magento's core succeed in doing this, cf. tables store and customer_entity, where store_id column is nullable.

[Edit] It seems that generating a db_schema_whitelist.json with command bin/magento setup:db-declaration:generate-whitelist --module-name=My_Module solved it, didn't exactly understood why, if anyone has an explanation I would be grateful!

Answer 5

In Magento 2.3 :

The <module_vendor>/<module_name>/etc/db_schema_whitelist.json file provides a history of all tables, columns, and keys added with declarative schema. It is required to allow drop operations. It can be generated manually or created automatically with the following command:

bin/magento setup:db-declaration:generate-whitelist [options] [options] can be:

--module-name[=MODULE-NAME] specifies which module to generate a whitelist for. If no module name is specified, then the default behavior is to generate a whitelist for all modules. You can also explicitly set --module-name=all.

When creating a new table, remember to generate the db_schema_whitelist.json file.

As a best practice, you should generate a new whitelist file for each release. You must generate the whitelist in any release that contains changes in the db_schema.xml file.

For more detail refer below link: https://devdocs.magento.com/guides/v2.4/extension-dev-guide/declarative-schema/migration-commands.html#create-whitelist

For specific Module execute below command:

sudo php bin/magento setup:db-declaration:generate-whitelist --module-name=PackageName_ModuleName

Answer 6

I had faced with this issue in php bin/magento setup:upgrade

SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'FK_8DFA69E89F93F6B2F7D11FEB4972429A', query was: ALTER TABLE amasty_mostviewed_pack_productMODIFY COLUMNentity_idint(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Entity ID", MODIFY COLUMNpack_idint(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT "Pack id", MODIFY COLUMNproduct_idint(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT "product id", ADD CONSTRAINTAMASTY_MOSTVIEWED_PACK_PRD_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE, ADD CONSTRAINT FK_8DFA69E89F93F6B2F7D11FEB4972429A FOREIGN KEY (pack_id) REFERENCES amasty_mostviewed_pack (pack_id) ON DELETE CASCADE

So I run

php bin/magento setup:db-declaration:generate-whitelist --module-name=Amasty_Mostviewed

this issue has been fixed