Integrity constraint violation: 1452 Cannot add or update a child row: a foreign
key constraint fails (`sumaria_magento/mg_catalog_category_product_index`,
CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_PRODUCT_ENTITY` FOREIGN KEY
(`product_id`) REFERENCES `mg_catalog_product_entity` (`entity_id)
Reason..
The Constraint dont find any Parent keys in parent table. But children for these Product ids are present. That means… database have Children entries without Parent …
Is it possible to break the referential integrity when a foreign key constraint is set on an InnoDB table? The answer is an (unfortunate?) yes. It’s not a bad feature that it’s possible to temporarily turn @@foreign_key_checks off, because sometimes it’s necessary to change data in a way that referential integrity needs to be broken during the modification process. But most of the time, the data should be valid, when modification is finished. So what can be done?
A little example demonstrates this:
mysql> CREATE TABLE table_one (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO table_one (id) VALUES
-> (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE table_two (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> id_table_one INT NOT NULL,
-> FOREIGN KEY (id_table_one) REFERENCES table_one(id))
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO table_two (id_table_one) VALUES
-> (3), (2), (2), (3), (1);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM table_one; SELECT * FROM table_two;
+—-+
| id |
+—-+
| 1 |
| 2 |
| 3 |
+—-+
3 rows in set (0.00 sec)
+—-+——-+
| id | id_table_one |
+—-+——-+
| 5 | 1 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 4 | 3 |
+—-+——-+
5 rows in set (0.00 sec)
mysql> INSERT INTO table_two (id_table_one) VALUES (4);
ERROR 1452 (23000): Cannot add or update a
child row: a foreign key constraint fails
(`test/table_two`, CONSTRAINT `table_two_ibfk_1` FOREIGN KEY
(`id_table_one`) REFERENCES `table_one` (`id`))
mysql> SET @@foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO table_two (id_table_one) VALUES
-> (2), (1), (4), (2);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SET @@foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM table_one; SELECT * FROM table_two;
+—-+
| id |
+—-+
| 1 |
| 2 |
| 3 |
+—-+
3 rows in set (0.00 sec)
+—-+——-+
| id | id_table_one |
+—-+——-+
| 5 | 1 |
| 7 | 1 |
| 2 | 2 |
| 3 | 2 |
| 6 | 2 |
| 9 | 2 |
| 1 | 3 |
| 4 | 3 |
| 8 | 4 |
+—-+——-+
9 rows in set (0.00 sec)
The record no 8 in table table_two has now an invalid value, due to the fact that we have set the foreign key verification temporarily to off (0).
Hello webmaster
I would like to share with you a link to your site
write me here preonrelt@mail.ru
Hi,
The article was nice but felt it was abruptly stopped from my learning point.
could you pl. tell me:
the check is temporarily off.
what will be my next steps:
a) can I add value ‘4′ to table_one later on ?
b) do I have make the foreign key check on later on ?
c) if so, when should I check it on? at what stage ?
or is it that, one the value ‘4′ is added to table-one, there is automatica synchrony between the two tables ?
pardon my lack of knowledge.
Thank you,
looking forward to hearing from you,
Prasad.
Hello Alexwebmaster
You are also welcome.
Thanks
–Rana
Hello Prasad
Thanks for visiting my blog and your intelligent comment. Yes you may add value ‘4′ to table_one later on and also you may check foreign key later on but follow the step carefully when you want to check ‘foreign key’ and table synchronization will be automatically.
Thanks and Regards
–Rana
The article is Really very nice. I was also getting this error. Now it is Solved.