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?

Answer 1

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.