Why in MySQL,
INSERT IGNORE INTO
SET FOREIGN_KEY_CHECKS = 0;
My solution is a work around to the problem and the actual solution will always be fixing the problem within the MySQL itself.
The following steps solved my problem:
a. Consider having the following tables and data:
mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL , PRIMARY KEY (id) ) ENGINE=INNODB; mysql> CREATE TABLE child (id INT AUTO_INCREMENT , parent_id INT , INDEX par_ind (parent_id) , PRIMARY KEY (id) , FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB; mysql> INSERT INTO parent VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); mysql> SELECT * FROM parent; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+
b. Now we need to delete some of the rows to demonstrate the problem:
mysql> DELETE FROM parent WHERE id IN (3, 5);
c. PROBLEM: The problem arises when you try to insert the following child rows:
mysql> INSERT IGNORE INTO child VALUES (NULL, 1) , (NULL, 2) , (NULL, 3) , (NULL, 4) , (NULL, 5) , (NULL, 6); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f ails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERE NCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) mysql> SELECT * FROM child; Empty set (0.00 sec)
Even though the
IGNORE keyword is used, but MySQL cancels the the requested operation because the generated error is not turned into warning (as it supposed to). Now that the problem is obvious, let's see how can we execute the last insert into statement without facing any error.
d. SOLUTION: I'm going to wrap the insert into statement by some other constant statements which are neither dependent on the records inserted, nor on their number.
mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> INSERT INTO child VALUES (NULL, 1) , (NULL, 2) , (NULL, 3) , (NULL, 4) , (NULL, 5) , (NULL, 6); mysql> DELETE FROM child WHERE parent_id NOT IN (SELECT id FROM parent); mysql> SET FOREIGN_KEY_CHECKS = 1;
I know that this is not optimum but as long as MySQL has not fixed the problem, this is the best I know. Especially since all the statements can be executed in one request if you use mysqli library in PHP.