Magento – Magento 2 : Foreign key constraint is incorrectly formed, ALTER TABLE customer_group

customerdatabaseforeign keymagento2

After upgrade from magento 2.1.7 to 2.2.3 and am stuck. I have tried everything.

In Pdo.php line 235:
SQLSTATE[HY000]: General error: 1025 Error on rename of './zwwnzwzjhe/#sql-1bd1_25f9' to './zwwnzwzjhe/customer_group' (errno:
150 – Foreign key constraint is incorrectly formed), query was: ALTER
TABLE customer_group MODIFY COLUMN customer_group_id int(10)
UNSIGNED NOT NULL AUTO_INCREMENT

In Pdo.php line 228: SQLSTATE[HY000]: General error: 1025 Error on rename of './zwwnzwzjhe/#sql-1bd1_25f9' to
'./zwwnzwzjhe/customer_group' (errno: 150 – Foreign key constraint is
incorrectly formed)

I am unable to delete this table or make any changes to this table …please help.

Thank you

Best Answer

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

Option 1:

Remove constraints
Perform TRUNCATE
Delete manually the rows that now have references to nowhere
Create constraints

Option 2:

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table customer_group; 
SET FOREIGN_KEY_CHECKS = 1;

Related Solutions

Magento – Magento import tables from old database show error (errno: 150 “Foreign key constraint is incorrectly formed”)

This error could be due to several reasons but you are working with imported tables so try checking ENGINE and/or CHARSET parameters: It can happen you are trying to add a new table using a different one!

From: MySql - Using FOREIGN KEY Constraints

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it.

So if you are using a .sql file to import the customer's tables, open it and check it: at the end of queries there should be something like:

) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

then be sure to use those same parameters for the new customer_entity table.

If the problem keep showing, check if foreign key column and the referencing have same column type or length, read more here.

Reed also here MariaDB: InnoDB foreign key constraint errors

Magento – Integrity constraint error referring to quote_item table while adding product to cart

I ran into the same problem when i tried to change data on quote items with the checkout_cart_product_add_after event. This event is fired after every product add, but before the cart is saved. Check the Magento\Checkout\Controller\Cart\Add line 108-113 for reference. So if you want to save the quote item on a new session there is no quote yet and there is no quote_id to reference to.

In my case, i just left the $item->save(); out, because it will be saved afterwards automatically.

Best Answer

Related Solutions

Magento – Magento import tables from old database show error (errno: 150 “Foreign key constraint is incorrectly formed”)

Magento – Integrity constraint error referring to quote_item table while adding product to cart

Related Topic