Can MySql quietly abandon inserts where foreign key fails?

Question

I am executing INSERT INTO Foo VALUES (a, b, c, ...) and sometimes one of the a, b, etc does not satisfy a foreign key constraint.

In this situation, I want the insertion to be not happen and for no error to be generated.

Can I do this in a single statement, or do I have to do a separate IF EXISTS?

Why not include a `SELECT` in your insert statement that joins the referenced table? This way, nothing would be return if the inner join fails. — woemler, Mar 28 '13 at 13:56
@willOEM I don't know what you mean -- join in an insert. That makes no sense to me. Can you write it as a full answer please? — spraff, Mar 28 '13 at 15:42
I tried INSERT IGNORE but `mysql_query` returns FALSE and I need it to return TRUE so that the error handling layer works properly. — spraff, Mar 28 '13 at 15:57

Sebas · Answer 1 · 2014-05-09T14:21:43.673

Yes, with the ignore keyword:

INSERT IGNORE INTO `foo` (...) VALUES (...);

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

On the other hand, if your concern is due to the fact you're inserting children before parents rows, you may as well DISABLE the constraints and ENABLE them after parents are inserted:

SET FOREIGN_KEY_CHECKS=0;

-- do your inserts not caring about foreign keys

SET FOREIGN_KEY_CHECKS=1;

But this is only if you know you respect data integrity.

On a side note, I think worth adding this comment about deferring constraints found in the mysql website:

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.

We aknowledge that MySQL innoDB may as well implement this feature in the future.

I already tried INSERT IGNORE. Either way I get `Cannot add or update a child row: a foreign key constraint fails` — spraff, Mar 28 '13 at 15:24
FYI Full query is `INSERT IGNORE INTO Foo (x_id, y_id, total) VALUES (2, 115, 1) ON DUPLICATE KEY UPDATE total=total+1` — spraff, Mar 28 '13 at 15:26
It's returning from php's `mysql_query` with something which tests to boolean false. — spraff, Mar 28 '13 at 15:28
I can't work out what mysql_query is returning. It's not NULL and it's not FALSE. Docs say it should be FALSE on an error of INSERT. What's going on? — spraff, Mar 28 '13 at 15:39
It is FALSE, actually, I just read the log wrong. I need it to return TRUE because otherwise I have to break encapsulation at the error handling layer. — spraff, Mar 28 '13 at 15:54
The error handler checks and reports all errors. This FALSE should not be considered an error, but I don't want the error checker to have to know that. — spraff, Mar 28 '13 at 16:18

score 0 · Answer 2 · answered Mar 28 '13 at 16:09

If you have full control over the structure of the SQL for your insert, you can try something like this:

insert into Foo (a, b, c)
select
    x.a,
    x.b,
    x.c
from 
    (select
        101 as a,
        202 as b,
        'ABC123' as c
    from dual ) x
    inner join other_table y
        on x.b = y.pkid

In this instance, since you are performing an inner join on your data and its parent table, if the foreign key value in your data does not have a corresponding value in the primary table, nothing will be inserted.

score 0 · Answer 3 · edited May 23 '17 at 11:50

The best way to do this is using:

INSERT INTO table (col1, col2) SELECT val1, val2 FROM table2 WHERE val3 = 123

When the WHERE part results to false then entire INSERT isn't executed. At the same time SELECT val1, val2 can contain any sub-query you want.

In your case you'll probably check if foreign keys exist in the WHERE condition.

See MySQL INSERT IF (custom if statements) for more detailed explanation.

Can MySql quietly abandon inserts where foreign key fails?

3 Answers3