Search code examples
mysqlsqlatomic

MySQL: Atomically inserting into a table and into a related table simultaniously

I have two tables, a TaskLists table with an automatically generated primary key and a Tasks table. A TaskList can have multiple Tasks.

How would I now insert a TaskLists row and at the same time atomically insert none to multiple rows into the Tasks table which reference the just inserted object.

Solution

  • According to general relativity, nothing in this universe happens at the "same time", so you can't. :)

    However, you can start a transaction, insert a row into TaskList, insert corresponding rows into Task and then commit the transaction. That would certainly be "atomic" in a sense that you couldn't (permanently) write partial data in the database.

    If TaskList's ID is auto-incremented, you can get it via LAST_INSERT_ID and then use it for Task's FK.