Re: MySQL Tutorial: LAST_INSERT_ID()
Michael:
I encountered a similar issue, until I realized what was happening...
According to the tutorial (p.214, Section 3.6.6 "Using Foreign Keys"), one should perform this pattern of INSERT commands:
(first person - Antonio)
INSERT INTO person VALUES (...);
INSERT INTO shirt VALUES(...), VALUES(...), ... ;
(second person - Lilliana)
INSERT INTO person VALUES (...);
INSERT INTO shirt VALUES(...), VALUES(...), ... ;
When I got the results similar to what you described, I added another step.
Between the 2 INSERT's for each person, I added this:
SELECT @last:=LAST_INSERT_ID();
Next, I substituted @last for each of the LAST_INSERT_ID()'s in the INSERT INTO shirt clauses.
That resolved the problem.
Here's what it looks like...
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last:=LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last:=LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
(Note: You should drop the tables & re-create them in order to get the table ID's back to zero)
I believe that LAST_INSERT_ID() is being incremented for each of the INSERT INTO shirt clauses.
Hope this helps.
Chris