MySQL using last auto incremented ID from a table in other tables

Question

I currently have the following statements.

INSERT INTO TripsTaken (ProfileId, DestinationLocation, Name, ImageUrl, StartDate, EndDate, Summary, Latitude, Longitude) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?);
INSERT INTO Transportation(Type, Location, StartDate, EndDate) VALUES (?, ?, ?, ?);
INSERT INTO TripsTakenTransportation (TripId, id) VALUES( (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) , LAST_INSERT_ID());
INSERT INTO Transportation(Type, Location, StartDate, EndDate) VALUES (?, ?, ?, ?);
INSERT INTO TripsTakenTransportation (TripId, id) VALUES( (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) , LAST_INSERT_ID());

I'm getting the following error:

{ [Error: ER_NO_REFERENCED_ROW_: Cannot add or update a child row: a foreign key constraint fails (tripsdatabase.tripstakentransportation, CONSTRAINT tripstakentransportation_ibfk_1 FOREIGN KEY (TripId) REFERENCES TripsTaken (TripId))] code: 'ER_NO_REFERENCED_ROW_', errno: 1452, sqlState: '23000', index: 2 }

The error occurs whenever the statement INSERT INTO TripsTakenTransportation... occurs twice. I suspect my sub query (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) is not doing what I expected the second time around.

How can I get the LAST_INSERT_ID() from a table, store it and reuse it in my other queries?

Note:

Transportation also has an auto increment column.

@Mate I might have missed something in reading the docs but I don't see a solution there to my problem. — andre, Mar 08 '14 at 02:08
convert (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) to (SELECT MAX(ID) FROM TripsTaken) or just use the primary key with auto increment in the case is not named ID — rusben, Mar 08 '14 at 02:17
@rusben There are many connections to the DB so if the another user changes it while I am working with the value things will go wrong. LAST_INSERT_ID() is a per session value. — andre, Mar 08 '14 at 02:23
I think the query is wrong. Probably you will like to use sql variables http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — rusben, Mar 08 '14 at 02:25

score 2 · Accepted Answer · answered Mar 08 '14 at 02:25

You have endowed LAST_INSERT_ID() with more intelligence than it has. You can't say MAX(LAST_INSERT_ID()) FROM table because it isn't tied to a table.

Here's the sort of thing you need:

INSERT INTO TripsTaken 
       (ProfileId, DestinationLocation, Name, 
        ImageUrl, StartDate, EndDate, Summary, Latitude, Longitude) 
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?);

SET @tripsID := LAST_INSERT_ID();

INSERT INTO Transportation
       (Type, Location, StartDate, EndDate) 
VALUES (?, ?, ?, ?);

INSERT INTO TripsTakenTransportation 
       (TripId, id)
VALUES (@tripsID , LAST_INSERT_ID());

LAST_INSERT_ID() returns the value of the very most recently incremented auto-increment column value. The good news is that there's a value of LAST_INSERT_ID() for every distinct connection to MySQL, so you can rely on it to be correct from SQL statement to statement within a connection. The bad news is you have be very careful not to edit your SQL code to add new INSERT statements, because they have the side-effect of changing LAST_INSERT_ID(). Hack with care.

Good stranger, you just saved my weekend. This solved my issue. Thanks. — andre, Mar 08 '14 at 02:41

MySQL using last auto incremented ID from a table in other tables

1 Answers1