Create 2 records in 2 tables consistently using MySQL/InnoDB

Question

Consider I have two tables, t1 and t2

t1 = (id, name), t2 = (id, fid)

The fid in t2 is a Foreign Key to t1.

Steps to create rows as following.

1. Insert a row in t1, get the id
2. Use that id and insert as fid in t2.

My problem is:

Since t1's id is unknow when the transaction is not committed, so how to perform the insertion to t2?

Answer 1

If id is auto-incremented in table1 then you can do something like this:

INSERT INTO t1 (name) VALUES ('whatever');
INSERT INTO t2 (fid) VALUES (LAST_INSERT_ID());

This comes from the MySQL Reference Manual.

EDIT: How about if I am inserting 3 tables t1, t2, t3 Both t2 and t3 have a fid equal to t1. But when t3 insert the fid, the LAST_INSERT_ID() is belong to t2, not t1.

Then you could do something like this:

INSERT INTO t1 (name) VALUES ('whatever');
SET @id=LAST_INSERT_ID();
INSERT INTO t2 (fid) VALUES (@id);
INSERT INTO t3 (fid) VALUES (@id);
...