Inserting an auto incrementing Primary Key into another table (to join with later)

Question

I'm pretty new to MySQL, and I hope i'm getting things right. I've set up a database with 3 tables with columns as below (Primary keys are PK):

Users table

• uid (PK)
• username

Directories table

• uid
• path
• project

DirInfo table

• infoID (PK)
• size
• dateofcheck
• exists
• pathID

When I insert NULL values to Directories.pathID, I get a new pathID (auto increment). How can I then insert that same pathID to DirInfo.pathID ?

Will I have to run multiple queries to INSERT and SELECT to keep each column in each table up to date, or am I doing things wrong.

Answer 1

The function LAST_INSERT_ID() returns the last value allocated by an auto-increment column during an INSERT. You can use this subsequently as you insert into a dependent table.

INSERT INTO Directories (pathId) VALUES (NULL); -- auto-increment
INSERT INTO DirInfo (pathId) VALUES ( LAST_INSERT_ID() );

Or you can set a MySQL user variable so you can use it repeatedly.

INSERT INTO Directories (pathId) VALUES (NULL); -- auto-increment
SET @pathId := LAST_INSERT_ID();
INSERT INTO DirInfo (pathId) VALUES ( @pathId );