MySQL Insert into Multiple Tables: Database Normalization

Insertion of data into multiple tables simultaneously in MySQL is not directly supported. However, there are alternative approaches to achieve this functionality.

Using Transactions

To ensure data consistency across tables, it is recommended to use transactions. Transactions encapsulate a series of queries as a single unit, guaranteeing that all queries either succeed or fail together.

Here's an example using transactions:

BEGIN;
INSERT INTO users (username, password) VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

Retrieving the Auto-Increment Value

To insert the auto-increment id from the users table into the profiles table, you can use the LAST_INSERT_ID() function. However, if the subsequent insert statement inserts into a table with its own auto-increment column, the LAST_INSERT_ID() value will be updated to that table's value.

To preserve the original LAST_INSERT_ID() value, you can store it in a custom variable within the transaction.

Using MySQL Variables:

INSERT ...
SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
INSERT INTO table2 (@mysql_variable_here, ...);

Using Language Variables:

// PHP example
$mysqli->query("INSERT INTO table1 ...");
$userid = $mysqli->insert_id;
$mysqli->query("INSERT INTO table2 ($userid, ...)");

Caution

It is important to consider database consistency when inserting into multiple tables. If interrupted, transactions ensure that all queries are either executed fully or not at all. Without transactions, partial inserts may result in inconsistencies.