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).
Alexwebmaster
Hello webmaster
I would like to share with you a link to your site
write me here preonrelt@mail.ru
Prasad
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.
S.M. Saidur Rahman
Hello Alexwebmaster
You are also welcome.
Thanks
–Rana
S.M. Saidur Rahman
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
Pranjali
The article is Really very nice. I was also getting this error. Now it is Solved.
Mark Won
Thanks for your good explanation on this.