Calculate non-standard auto-increment automatically

The table I am working with does not have a standard auto-increment field to use as a primary key, so I need to come up with a way to automatically calculate the value that should be used in the field.

My first thought was to create a trigger to happen AFTER INSERT, however, as far as I can tell, there's no easy way to reference the row that was just inserted. I could do something like

UPDATE `table` SET `reference_number` = (SELECT ....) WHERE `reference_number` IS NULL

but because reference_number is a PRIMARY KEY, it cannot be null. (Does that mean it would be an empty string ''?)

Is there a better way to do this?

Answers


CREATE TRIGGER mkuuid BEFORE INSERT ON SomeTable
 FOR EACH ROW BEGIN
   SET NEW.primary_key = UUID_SHORT();
 END

Need Your Help

Travis-CI: Can the version of Node installed on all VM images match the latest (stable) on the node.js images?

node.js gulp travis-ci

I'm running my CI through Travis and using Gulp to compile my assets for a project where the main language is not Node. I'm therefore using the version of Node they give you on all VM images for th...