[SOLVED] MySQL Multi-Insert? MySQL DB integrity after failed INSERT

MySQL Multi-Insert? MySQL DB integrity after failed INSERT

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?

Solution

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!