INSERT the key into multiple tables in MySQL

Foreign key relationships may occasionally need to be inserted at the same time as the primary key. We won’t know what the primary key will be to use in the child tables of a table until it isn’t stored there.

What if we had a solution that simultaneously inserted values into the primary table and all of its dependent tables?

Here is an example that lets you know how to insert into multiple tables at the same time.

DROP TABLE IF EXISTS tbOrdersHeader;
CREATE TABLE tbOrdersHeader(
OrderID INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
OrderDate DATETIME NOT NULL);

DROP TABLE IF EXISTS tbOrdersDetail;
CREATE TABLE tbOrdersDetail(
OrderDetailID INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
OrderID INT NOT NULL REFERENCES tbOrdersHeader(OrderID),
ProductID INT NOT NULL);

BEGIN;
INSERT INTO tbOrdersHeader (OrderDate) VALUES(‘2023-03-14’);
INSERT INTO tbOrdersDetail (OrderID, ProductID) VALUES(LAST_INSERT_ID(),10012);
COMMIT;

SELECT * FROM tbOrdersHeader;
SELECT * FROM tbOrdersDetail;