MySQL's INSERT IGNORE INTO & foreign keys

Question

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

Answer 1

INSERT IGNORE is just a workaround for the lazy. You shouldn't be inserting duplicates records in the first place. Besides a Primary/Unique is not the same as a Foreign key. And have in mind that IGNORE will also ignore other error and warnings (division by zero, data truncations), which usually is not a good thing.

In this case, and almost every time, it makes more sense to use REPLACE instead of INSERT IGNORE. Another option is ON DUPLICATE KEY UPDATE.

In your case, if you can't remove the missing parents before the insert, you can manage the same thing with a temporary table like this:

create temporary table tmpTable (col1, col2, ...);
insert into tmpTable values (row1), (row2), ...; -- same insert you already had
alter table tmpTable add key (foreignColumnName); -- only if # of rows is big
insert into table select null /* AI id */, col1, col2 ...
  from tmpTable join parentTable on foreignColumnName = parent.id;

Regards, Jose.

Answer 2

[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.

Answer 3

I believe INSERT IGNORE is intended to ignore errors from the server layer, not the storage engine layer. So it will help for duplicate key errors (it's primary use case) and certain data conversions, but not foreign key errors, which come from the storage engine layer.

As for your specific requirement:

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

For that I recommend using mysql -f to force it to keep running despite any errors. So for example, if you have a file like this:

insert into child (parent_id, ...) values (bad_parent_id, ...);
insert into child (parent_id, ...) values (good_parent_id, ...);

Then you can load that file like so, which will insert the good rows and ignore the error from the bad rows:

mysql -f < inserts.sql

Answer 4

If you are inserting a row into the database, you can run this check explicitly using an additional query. Say you have these tables:

User                               Pet
userId | userName | petId          petId | petName
-------+----------+------          ------+----------
 1     | Harold   | 8               1    | Fido
 2     | Fred     | 3               3    | Spot
                                    8    | Mittens

and you want to insert a new user (George, who has pet #1). You could do something like

$newUserName = "George"
$petId = "1"
mysql_query("begin")
$result = mysql_query("SELECT petId FROM Pet WHERE petId = $petId LIMIT 1")
if ($result && 1 == mysql_num_rows($result)) {
    mysql_query("INSERT INTO User (userName, petId) VALUES ('$newUserName', $petId)")
}
mysql_query("commit")

Please excuse the overly simplified code and bad practices in my example -- it's just meant as illustrative of a possible work-around.

Answer 5

This issue seems to be fixed in MySQL 5.7, see https://bugs.mysql.com/bug.php?id=78853.

Now foreign key constraint error is turned into warning instead:

This issue exists in 5.1,5.5,5.6 builds but I see some improvements done in 5.7 where error has been converted to warning instead of error after WL#6614.

Answer 6

I think the simplest, mysql solution to the problem is joining into the foreign key table to verify constraints:

INSERT INTO child (parent_id, value2, value3)
SELECT p.id, @new_value2, @new_value3
FROM parent p WHERE p.id = @parent_id

But it seems strange that you want to throw away records based on missing foreign keys. I, for instance, prefer having INSERT IGNORE error on foreign key checks.