How to use LAST_INSERT_ID() when using uuid as a default value?

Question

I have a table where I auto-generate a binary(16) Id by having this in my defaultExpression:

(uuid_to_bin(uuid(), true))

I created a store procedure and insert values to that table and figured I could retrive the latest value by using LAST_INSERT_ID() but Im getting the value 0 instead.

Example:

DECLARE userId BINARY(16);

INSERT INTO Users (Email, Password, FirstName, LastName, Age, Jobtitle, ImageId, PrefLang, ChangedPassword)
VALUES ('email', 'Hashpassword', 'firstName', 'lastName', 'age', 'jobTitle', 'imageId', 'prefLanguagId', 0);

SET userId = LAST_INSERT_ID(); // not getting the correct value here

Trying to use the value above in a junction table:

  INSERT INTO UserRoles (UserId, RoleId)
    VALUES (userId, roleId);

Error Code: 1452 Cannot add or update a child row: a foreign key constraint fails (`dbName`.`userroles`, CONSTRAINT UserIdRef FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`))

How can I solve this?

You can only use LAST_INSERT_ID() when using an auto increment column. — Barmar
– Barmar, Commented May 11, 2020 at 20:08
Im more use to working in a sql enviorment and thought it would be the same as IDENTIY. Only way to solve this is by adding the UUID manualy in the sp? — ThunD3eR
– ThunD3eR, Commented May 11, 2020 at 20:10

Barmar · Accepted Answer · 2020-05-11 20:12:22Z

4

LAST_INSERT_ID() only gets an ID that was assigned using AUTO_INCREMENT. It won't return an ID that was assigned using a DEFAULT expression.

You'll need to calculate the ID in your procedure rather than using the default.

SET userId = uuid_to_bin(uuid(), true);
INSERT INTO Users (userId, Email, Password, FirstName, LastName, Age, Jobtitle, ImageId, PrefLang, ChangedPassword)
    VALUES (userId, 'email', 'Hashpassword', 'firstName', 'lastName', 'age', 'jobTitle', 'imageId', 'prefLanguagId', 0);
INSERT INTO UserRoles (UserId, RoleId)
    VALUES (userId, roleId);

answered May 11, 2020 at 20:12

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ThunD3eR Over a year ago

Did not know that this was not possible. I firgured that the above would be solution as soon as I read you comment. Probably good to save this question a a future reference.

Collectives™ on Stack Overflow

How to use LAST_INSERT_ID() when using uuid as a default value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related