MySQL Multi-Insert? MySQL DB integrity after failed INSERT

Question

Is it possible to insert a row into multiple tables at once? If you do several tables related by an ID; what is the best way to ensure integrity is maintained in case an INSERT fails?

Answer 1

Use transactions, luke.

Answer 2

That's exactly what transactions are for. If any of the commands fail, the whole thing since START TRANSACTION is rolled back:

START TRANSACTION;
INSERT INTO sometable VALUES(NULL,'foo','bar');
INSERT INTO someothertable VALUES (LAST_INSERT_ID(),'baz');
COMMIT;

This being MySQL, you can't use transactions with MyISAM tables (you'll need the tables to use some engine that supports this, probably InnoDB).

This will never be inserted into the table (normally you'd have some branching, e.g. an IF):

START TRANSACTION;
INSERT INTO sometable VALUES(NULL,'data','somemoredata');
ROLLBACK;

Caveat: SQL commands which change the database structure (e.g. CREATE,ALTER,DROP) cannot be rolled back!

Answer 3

MySQL can insert multiple rows (search for 'multiple rows') like this:

INSERT INTO table (field1, field2, ...) VALUES (value1, value2), (value3, value4), etc...

However, there's no way to tell what got inserted and what wasn't due to constraint violations, beyond the query returning a count of records, duplicates, and warnings. You also can't use last_insert_id() to figure out the IDs of the new rows, as that only returns the LAST id that was created, not a set of ids.

If you need to guarantee integrity, then use single row insert statements and transactions.