Hi Don, > I'm new on the list, and just had a general question about the best > approach for setting up a multi-table insert. I know that MySQL > doesn't support this outright, but would most people recommend using a > transaction?
This question is essentially neutral to the language-specific API that you're using. What you're describing is *exactly* when you should use a transaction. Bear in mind that all tables involved in the transaction will need to use the InnoDB storage engine if you want the transaction to make any sense (i.e. work as you hope). > In my DB app, I have a table, person, that uses foreign keys from > other tables (3-4). When I add someone to the person table, I want > the data for their address to be inserted into the address table as > well. Other data needs to be inserted into additional tables. BEGIN INSERT INTO people (...) VALUES (...) id = last_insert_id() INSERT INTO address (person_id, ...) VALUES (id, ...) INSERT INTO other (person_id, ...) VALUES (id, ...) COMMIT Check the return values of each of your statements and if anything is weird, ROLLBACK the transaction. Likewise, if the COMMIT fails or if you have to rollback, you should design your application so that it will retry the transaction in case the transaction could not be committed. This can happen because of deadlocks or other reasons, and a transaction-aware application should be able to deal with that. Regards, Kolbe _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
