Description:
The purpose of the IGNORE keyword (e.g. INSERT IGNORE) is
to allow one to INSERT/UPDATE large numbers of rows,
ignoring any that contain invalid data. The idea is that it will
normally be used when recopying already verified data, and
that any invalid data found will be further investigated by the
user -- and that all the valid data will be accepted, regardless
of the invalid data errors found.
This is not happening with InnoDB tables that have a foreign
key. In this case, the SQLSTATE 23000: Cannot add or update a
child row: a foreign key constraint fails error rejects the
entire data change.
Monty would like this fixed for MySQL 5.0; IRC log follows:
<trudy> monty: paul: If primary key table has {10} and I do INSERT IGNORE INTO
foreign_table VALUES (10),(12),(10); the result is that the entire INSERT is rejected,
because the value {12} is not in the primary key table. Note that this is with InnoDB
tables.
<monty> We *should* ignore foreign key check failures for future rows
<paul> But should the statement roll back?
<monty> paul: in other words, we should fix the above, but probaly in 5.0 and not in
4.1
<monty> no
<monty> not with ignore
<trudy> monty: To clarify, in my example, you think we should allow the INSERT of the
2 rows that match the primary key, but disallow the "invalid" value, correct?
<monty> yes
<monty> (sorry, didn't see your example)
<trudy> monty: I inserted 10,12,10 into foreign key but primary key only has 10.
<monty> you mean in the other table
<monty> in that case, you should just get the two matching rows inserted
<trudy> That's right: the primary key table has 1 row, with 10 in it.
<trudy> monty: OK, I'll file a bug report to that effect, then.
<trudy> You want this fixed for 5.0, correct?
<monty> yes
How to repeat:
mysql> create table t1 (col1 int primary key) engine=innodb;
mysql> insert into t1 values (10),(15);
mysql> create table t2 (col1 int, key(col1), foreign key(col1) references t1(col1))
engine=innodb;
mysql> insert into t2 values(10),(12),(10);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql> select * from t2;
-- This shows the correct response, under both regular and 'traditional' sql_mode
mysql> insert ignore into t2 values(10),(12),(10);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql> select * from t2;
-- This is the incorrect response. While the row containing {12} is correctly
rejected because it is not found in the primary key, the other two rows should
be accepted because of the IGNORE. Table t2 should thus have 2 rows,
each with a value of {10}.
mysql> insert into t2 values (10);
mysql> update t2 set col1=12,col1=15,col1=12;
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql> select * from t2;
+------+
| col1 |
+------+
| 10 |
+------+
-- This is the correct response.
mysql> update ignore t2 set col1=12,col1=15,col1=12;
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql> select * from t2;
+------+
| col1 |
+------+
| 10 |
+------+
-- This is the incorrect response. The presence of IGNORE should change
the row from {10} to {15}, since {15} is found in the primary key table.
Description: The purpose of the IGNORE keyword (e.g. INSERT IGNORE) is to allow one to INSERT/UPDATE large numbers of rows, ignoring any that contain invalid data. The idea is that it will normally be used when recopying already verified data, and that any invalid data found will be further investigated by the user -- and that all the valid data will be accepted, regardless of the invalid data errors found. This is not happening with InnoDB tables that have a foreign key. In this case, the SQLSTATE 23000: Cannot add or update a child row: a foreign key constraint fails error rejects the entire data change. Monty would like this fixed for MySQL 5.0; IRC log follows: <trudy> monty: paul: If primary key table has {10} and I do INSERT IGNORE INTO foreign_table VALUES (10),(12),(10); the result is that the entire INSERT is rejected, because the value {12} is not in the primary key table. Note that this is with InnoDB tables. <monty> We *should* ignore foreign key check failures for future rows <paul> But should the statement roll back? <monty> paul: in other words, we should fix the above, but probaly in 5.0 and not in 4.1 <monty> no <monty> not with ignore <trudy> monty: To clarify, in my example, you think we should allow the INSERT of the 2 rows that match the primary key, but disallow the "invalid" value, correct? <monty> yes <monty> (sorry, didn't see your example) <trudy> monty: I inserted 10,12,10 into foreign key but primary key only has 10. <monty> you mean in the other table <monty> in that case, you should just get the two matching rows inserted <trudy> That's right: the primary key table has 1 row, with 10 in it. <trudy> monty: OK, I'll file a bug report to that effect, then. <trudy> You want this fixed for 5.0, correct? <monty> yes How to repeat: mysql> create table t1 (col1 int primary key) engine=innodb; mysql> insert into t1 values (10),(15); mysql> create table t2 (col1 int, key(col1), foreign key(col1) references t1(col1)) engine=innodb; mysql> insert into t2 values(10),(12),(10); ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails mysql> select * from t2; -- This shows the correct response, under both regular and 'traditional' sql_mode mysql> insert ignore into t2 values(10),(12),(10); ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails mysql> select * from t2; -- This is the incorrect response. While the row containing {12} is correctly rejected because it is not found in the primary key, the other two rows should be accepted because of the IGNORE. Table t2 should thus have 2 rows, each with a value of {10}. mysql> insert into t2 values (10); mysql> update t2 set col1=12,col1=15,col1=12; ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails mysql> select * from t2; +------+ | col1 | +------+ | 10 | +------+ -- This is the correct response. mysql> update ignore t2 set col1=12,col1=15,col1=12; ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails mysql> select * from t2; +------+ | col1 | +------+ | 10 | +------+ -- This is the incorrect response. The presence of IGNORE should change the row from {10} to {15}, since {15} is found in the primary key table.