MySQL SELECT LAST_INSERT_ID() for compound key. Is it possible?

Question

Can I get the LAST INSERT ID() for a compound key in MySQL?

Answer 1

Yes. You can't have multiple auto-increment fields in a single table.

CREATE TABLE foo (
  id1 int(11) NOT NULL auto_increment,
  id2 int(11) NOT NULL default '0',
  PRIMARY KEY  (id1, id2)
);

INSERT INTO foo VALUES (DEFAULT, 2);

SELECT LAST_INSERT_ID(); -- returns 1, the value generated for id1

LAST_INSERT_ID() returns the value only for a column declared AUTO_INCREMENT. There's no function to return the value in a compound primary key that wasn't generated by the system. You ought to know that value already, since you just gave it in an INSERT statement. The tricky case would be when a trigger or something overrides the value.