In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as
AUTO_INCREMENT. For example consider the following table:
CREATE TABLE `ExampleTable` ( `KeyPartA` mediumint(8) unsigned NOT NULL, `KeyPartB` smallint(5) unsigned NOT NULL, `Data` text NOT NULL, PRIMARY KEY (`KeyPartA`,`KeyPartB`) ) ENGINE=InnoDB;
Now to simulate
AUTO_INCREMENT, I learned, it is possible to create the following trigger
DELIMITER $$ CREATE TRIGGER ExampleAutoIncrement BEFORE INSERT ON ExampleTable FOR EACH ROW BEGIN SET NEW.KeyPartB = ( SELECT IFNULL(MAX(KeyPartB), 0) + 1 FROM ExampleTable WHERE KeyPartA = NEW.KeyPartA ); END $$ DELIMITER ;
Now inserts work correctly, generating auto-incremented values. Unfortunately I can not think of a sensible way to get the newly created key value.
LAST_INSERT_ID() nor its C counterpart return it, as there is no actual
AUTO_INCREMENT column. Also it is not possible to set the value with
LAST_INSERT_ID(IFNULL(MAX(KeyPartB), 0) + 1) inside the
ExampleAutoIncrement trigger, because MySQL resets the value after the trigger finishes.
The only way I thought of was to issue a
SELECT MAX(KeyPartB) FROM ExampleTable WHERE KeyPartA = ? after an insert, which defeats the whole purpose of the trigger. I.e. I could just as well do this before the insert and increment manually.
Is there any way to get the incremented key value from the trigger or some other means of simulating the
AUTO_INCREMENT I want?