3

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828

1 Answers1

7

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • "You can't have multiple auto-increment fields in a single table." I did not know this, so I tried to create a table with two auto-increment fields, and I got this error: Incorrect table definition; there can be only one auto column and it must be defined as a key. – Liam Dec 02 '15 at 21:57
  • So, 7 years later, the answer holds. – Sebastián Grignoli Jun 29 '16 at 02:21