Magento 2 Setup:Upgrade giving error of Cannot add foreign key constraint

Question

I have moved my website files and DB from one server to another.

Now, When I am running setup:upgrade command. I am getting below error:

Updating modules: Schema creation/updates:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, query was: ALTER TABLE quote_item MODIFY COLUMN item_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Item Id", ADD CONSTRAINT PRIMARY KEY (item_id), ADD CONSTRAINT QUOTE_ITEM_PARENT_ITEM_ID_QUOTE_ITEM_ITEM_ID FOREIGN KEY (parent_item_id) REFERENCES quote_item (item_id) ON DELETE CASCADE, ADD CONSTRAINT QUOTE_ITEM_QUOTE_ID_QUOTE_ENTITY_ID FOREIGN KEY (quote_id) REFERENCES quote (entity_id) ON DELETE CASCADE, ADD CONSTRAINT QUOTE_ITEM_STORE_ID_STORE_STORE_ID FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE SET NULL, ADD INDEX QUOTE_ITEM_PARENT_ITEM_ID (parent_item_id), ADD INDEX QUOTE_ITEM_PRODUCT_ID (product_id), ADD INDEX QUOTE_ITEM_QUOTE_ID (quote_id), ADD INDEX QUOTE_ITEM_STORE_ID (store_id)

---

Please help to get rid of this error.

• Vishal Thakur: Did you found any solution for this error. if Yes, Please Share the Solution. Thanks.
• sumeet bajaj: Did you find this solution ? i am also getting this.
• Balwant Singh: @sumeetbajaj: Did you find this solution? I am also getting this.
• Balwant Singh: @VishalThakur: Did you find this solution? I am also getting this.
• Vishal Thakur: @BalwantSingh no bro.

Answer 1

You need to run this query:

Select item_id from quote_item where parent_item_id is not null and parent_item_id in (select item_id from quote_item)

After that, the result will be updated and you need to fix it to parent_item_id = null.

Leave a comment if it works!

• Balwant Singh: Not worked for me.

Answer 2

I am getting the same error from last five days when I tried to upload on godaddy server from localhost. It is working fine at localhost but shown following error on server:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, query was: ALTER TABLE quote_item MODIFY COLUMN item_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Item ID", ADD CONSTRAINT PRIMARY KEY (item_id), ADD CONSTRAINT QUOTE_ITEM_PARENT_ITEM_ID_QUOTE_ITEM_ITEM_ID FOREIGN KEY (parent_item_id) REFERENCES quote_item (item_id) ON DELETE CASCADE, ADD CONSTRAINT QUOTE_ITEM_QUOTE_ID_QUOTE_ENTITY_ID FOREIGN KEY (quote_id) REFERENCES quote (entity_id) ON DELETE CASCADE, ADD CONSTRAINT QUOTE_ITEM_STORE_ID_STORE_STORE_ID FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE SET NULL, ADD INDEX QUOTE_ITEM_PARENT_ITEM_ID (parent_item_id), ADD INDEX QUOTE_ITEM_PRODUCT_ID (product_id), ADD INDEX QUOTE_ITEM_QUOTE_ID (quote_id), ADD INDEX QUOTE_ITEM_STORE_ID (store_id)

Kindly help

• Balwant Singh: Did you find this solution? I am also getting this.

Answer 3

If you are adding foreign key with the catalog_product_entity table or any other Magento2 core table then you need to match the column data type and other information with the table you are creating for your module. They both should match.

Answer 4

To resolve this issue i would suggest you to run below commands in sequence :

rm -rf generated; \
php bin/magento indexer:reindex; \
php bin/magento setup:upgrade; \
php bin/magento setup:di:compile; \
php bin/magento setup:static-content:deploy; -f \
php bin/magento cache:clean; \
php bin/magento cache:flush

then check also after running if its still not resolved then i would suggest to enabled developer mode for your setup and then run the same bunch of commands in sequence and then check !

Hope it helps