I used a trigger to generate id: ug01, ug02... I want to insert them into the foreign key column but last_insert_id() returns 0 creating an error. How can I get the value of the "last_insert_id" as it is in the primary key column.
Asked
Active
Viewed 194 times
0
Beulah Ana
- 301
- 2
- 14
-
Take a look at this answer: http://stackoverflow.com/questions/6499389/mysql-getting-last-insert-id-in-a-trigger – Lance Dec 29 '15 at 07:33
-
Generating an id yourself makes no sense at all. Also, `last_insert_id()` returns the value generated by `auto_increment` field. You can't use `last_insert_id()` to retrieve some random id **you** generated. You should *always* use a numeric id. If you need other characters for whatever reason, save them in another column and then concatenate the id and alphabet part while `SELECT`-ing. That will let you have your alphanumeric id, and MySQL will play along and won't have any issues caused by a weird primary key. – Mjh Dec 29 '15 at 10:05
1 Answers
0
As stated in the MySQL documentation, LAST_INSERT_ID() returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.
In your case, you are inserting the id, so an AUTO_INCREMENT value is not generated, thus LAST_INSERT_ID returns 0.
Cross check this by execute MySql command: SELECT LAST_INSERT_ID();
AddWeb Solution Pvt Ltd
- 18,949
- 3
- 21
- 50
-
How do I get the value to insert into the foreign key column as it is in the primary key column – Beulah Ana Dec 29 '15 at 09:49
-
-
-
here's the ref for last_insert_id, http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id – AddWeb Solution Pvt Ltd Dec 29 '15 at 10:30