Magento 2 Integrity Constraint Violation Issues & Solutions

In the following article, you will find the most common Magento 2 integrity constraint violation issues described. Below, we combine answers from different platforms to cover as many examples as necessary for keeping this material epistemic and clear.  You can find more tips in this article: Magento 2 Cookbook.

What is the root of all evil when it comes to Magento 2 integrity constraint violations? Duplicate entries are a reason for numerous issues caused by integrity constraint violations in Magento 2. Below, we provide several examples of how to discover and fix them. Although the described situations may look similar at first glimpse, they have slightly different solutions. Let’s start our exploration of integrity constraint violation in Magento 2 from the upgrade to newer versions. 

Integrity constraint violation on setup:upgrade 

AntondeG shares the following Magento 2 integrity constraint violation issue. After updating to 2.3.3, the author gets an error related to the Temando_Shipping module. To solve the problem, AntondeG disabled the module. Unfortunately, this solution caused another problem – errors while editing products. An attempt to rerun the magento setup:upgrade command leads to the following integrity constraint violation issue:

So, what was the solution? According to kolaente, there is an efficient workaround. However, it is not a serious solution. The author thinks that it is a bug in temando/module-shipping-m2 required by magento/product-community-edition. Since the data is added in vendor/temando/module-shipping-m2/Setup/UpgradeData.php:73, kolaente proposes to check the difference between the content in the database and the data inserted during the module upgrade. The author provides the following command to do that:

If the data is identical, it is necessary to delete it. Use this command:

After that, it is possible to rerun the bin/magento setup:upgrade command.

Follow this link for further information: Magento 2 integrity constraint violation on setup:upgrade.

Integrity constraint violation when upgrading Magento 2

This case describes an issue that occurs during the Magento upgrade from 2.1.x to 2.2.x. The corresponding error looks as follows:

The author proposes two solutions to fix this Magento 2 integrity constraint violation problem

I

First of all, you have to visit the following location and modify the Rule.php file with the empty data condition.

The author of the solution recommends to wrap it in

Now, it is necessary to run this command via SSH in the root of your Magento installation: 

That’s it. Let’s take a look at the second solution.

II

Before going any further, you have to create a backup of the database and tables you will work with. The current solution deletes all data related to sales rules and coupons. Run the following Query in PhpMyAdmin to truncate tables:

Now, you can run php bin/magento setup:upgrade via SSH in the Magento installation root. For further information, hit this link: How to fix Integrity constraint violation when upgrading Magento 2.

Integrity constraint violation during Magento 2 cron setup

MyStackExchange user reports about an integrity constraint violation when setting up cron for Magento 2.2.2. However, nothing seems to be updating. The author of the discussion provides the following error message:

dev_67Commerce proposes a solution. The author of the answer describes the entry key from the error message, pointing to the cause of the issue:

• 4639 – product_id
• 168 – attribute_id
• 1 – store_id
• 283 – attribute_value_id 

Next, dev_67Commerce supposes that the attribute type of 168 is varchar and concludes that its values are available in catalog_product_entity_varchar.

Now, it is necessary to search for attribute 168 along with entity_id 4639. This step reveals the content of the ‘value‘ column. As a result, you can remove all duplicate values: the issue is caused by the same comma-separated values of value_id 283 (from the error above).

After you’ve fixed the content of the table, save your work, flush cache, and re-run the index command. Repeat the above actions if similar integrity constraint violations for other products take place. You can see more details here: SQLSTATE[23000]: Magento 2 integrity constraint violation.

Integrity constraint violation during Magento 2 migration 

There is another approach to the issue we’ve just described. This time, the problem occurs while running the data migration tool. The author of the material constantly came across the following error message. 

The solution is similar to the one we’ve just described: you need to delete duplicate entries. However, the conditions are not the same, so the methods also differ:

• Look for duplicate entries in Magento 2. Most of them are situated in the database under the report_event and customer_visitor tables (look in other tables too).
• Eliminate the duplicate entries. 

However, it’s not all that simple. The author of the article reports that the duplicate entry message reappeared on every data migration tool run with a different duplicate entry. The logged files of Magento contained the error-causing duplicates, so it was decided to remove the log script from the data migration tool’s config.xml file:         

No more duplicate errors occurred, so the migration from Magento 1 to Magento 2 was completed successfully. You can find more information here: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry.

The impact of MySQL foreign_key_checks on the entire database

Another subject related to integrity constraint violations in Magento 2 was touched on in a StackExchange discussion. Sean Nguyen asked whether the SET FOREIGN_KEY_CHECKS=0; command affects the whole engine or a current transaction?

Almo answers that it is both for scope. You can set the command for a session like this:

If you need to set globally, choose the following one:

Ron Inbar further explains that the two foreign_key_checks variables include a global variable and a local one. The latter is applied per session. Besidesб it can be initialized to the value of a global variable upon connection.

Bouke Versteegh adds the following example to illustrate what the previous author said: 

Bouke also adds that setting the GLOBAL variable leads to a situation when the local one isn’t changed for any existing connections. It is necessary to reconnect to enable changes. Alternatively, it is possible to set the local variable.

The author also highlights another crucial aspect: MySQL does not enforce foreign keys for re-enabled FOREIGN_KEY_CHECKS. As a result, you may be stuck with an inconsistent database even with enabled foreign keys and checks.

To make foreign keys completely consistent, add them while checking is enabled. More tips on MySQL foreign_key_checks are available here.

How to disable a MySQL foreign key constraint temporarily

And we cannot talk about integrity constraint violations without providing tips on how to temporarily disable a foreign key constraint in MySQL. Jul asks this question and describes the following situation: there are two Django models with a ForeignKey to one another. It is impossible to delete the instances of a model since it leads to an error caused by the ForeignKey constraint:

Other forum users describe how to temporarily disable constraints and delete the instances. Thus, Andrew Campbell proposes to try DISABLE KEYS. The author of the answer recommends using this command:

And don’t forget to use this one afterwards:

According to Berniey, you can do that only for a single user mode maintenance. Otherwise, you may face data inconsistency issues. Berniey considers this approach helpful for uploading large amounts of data via a mysqldump output.

AntonioCS recommends disabling foreign key constraints to truncate tables:

You can find more answers here: How to temporarily disable a foreign key constraint in MySQL.