MySQL’s INSERT IGNORE INTO & foreign keys

foreign-keysinsertMySQL

Why in MySQL, INSERT IGNORE INTO does not change the foreign key constraint errors into warnings?

I'm trying to insert a number of records into a table and I expect MySQL to leave out the ones that result in error, any error, and insert the rest. Does anyone have any suggestions?

And the SET FOREIGN_KEY_CHECKS = 0; is not my answer. Because I expect the rows which defy the constraints not to be inserted at all.

Thanks

Best Answer

[NEW ANSWER]

Thanks to @NeverEndingQueue for bringing this up. It seems MySQL has finally fixed this issue. I'm not sure which version this problem was first fixed in, but right now I tested with the following version and the problem is not there anymore:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.22                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

To be clear:

mysql> INSERT IGNORE INTO child
    -> VALUES
    ->     (NULL, 1)
    ->     , (NULL, 2)
    ->     , (NULL, 3)
    ->     , (NULL, 4)
    ->     , (NULL, 5)
    ->     , (NULL, 6);
Query OK, 4 rows affected, 2 warnings (0.03 sec)
Records: 6  Duplicates: 2  Warnings: 2

To better understand the meaning of this last query and why it shows the problem is fixed, please continue with the old answer below.

[OLD ANSWER]

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.

Related Topic