Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is there a way to get the primary key with just one insert statement?

 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I insert a row into a table, there is a primary key generated automatically. I need to know that primary key before commit the transaction.
Currently, I'm using a second select statement (something like: select max(pk) from table_name) to get the primary key just created.
It works fine as long as I have a correct isolation level setting(e.g., SERIALIZABLE).

Is there a way to make the insert statement return me the primary key for the row just inserted? I'm using MySQL with plain SQL.
This is a SQL question, not JDBC.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Todd,
It's possible in JDBC, but not in pure SQL. The reason is that the JDBC drivers are handling the lookup for you and returning it in one shot. From a raw SQL point of view, you can only do one thing at a time. An insert or a select.

Almost. A stored procedure can give the illusion of doing multiple things. But then the stored proc is making multiple SQL calls.
 
Paul Clapham
Sheriff
Pie
Posts: 20945
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Todd Jain wrote:Currently, I'm using a second select statement (something like: select max(pk) from table_name) to get the primary key just created.


There's a nasty assumption built into that statement which may or may not be universally correct. The official MySQL way to get the primary keys from the row just added is

At least, that's what I'm using.
 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul, Thanks for your suggestion.

At which point, do you think "select max(pk) from table_name" will break?
 
Paul Clapham
Sheriff
Pie
Posts: 20945
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When the key-assignment algorithm decides to stop assigning the next sequential value. Or when its next sequential value is less than the primary key of another record where your code assigned a primary key value.

Isn't that just an assumption you have made, that the key-assignment algorithm will always assign a number larger than any other key in the file, or is it guaranteed by the way you defined the primary key?
 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I defined the primary key as "INT UNSIGNED NOT NULL AUTO_INCREMENT". It must always increase. What is the max it can go? I do not know what happens when it
reach the max. DB crash? I think "LAST_INSERT_ID() " also assume that the paimary key is an int.
 
Paul Clapham
Sheriff
Pie
Posts: 20945
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if you let the auto-incrementer assign key values of 1, 2, and 3 for three records, and then you write a fourth record specifying that the primary key is 4807, what is the next value that the auto-incrementer will return?

I don't know the answer to that. And I don't need to find out because I already know a reliable way to get the key value which the auto-incrementer assigned. I don't have to assume that it's going to be 4808 and then be caught out because it was actually 4.
 
Phil Freihofner
Ranch Hand
Posts: 120
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Couldn't there also be concurrency issues with the SELECT Max(pk) form? Suppose another user creates a record in between the time when the first record is inserted and the second SQL select is issued?

I'd stick with the "official" MySQL format unless there was a very good reason not to.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, unless you do both statements in a transaction (read committed or above) in which case the max from your transaction's point of view is the last insert. Its an inherently risky strategy otherwise.
 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Phil Freihofner wrote:Couldn't there also be concurrency issues with the SELECT Max(pk) form? Suppose another user creates a record in between the time when the first record is inserted and the second SQL select is issued?

I'd stick with the "official" MySQL format unless there was a very good reason not to.


This should not be a problem if I set isolation level setting as SERIALIZABLE.
 
xsunil kumar
Ranch Hand
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are using any ORM framework, then you can get the Primary key immediately stored in the object after transaction commit
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tejas Jain wrote:
Phil Freihofner wrote:Couldn't there also be concurrency issues with the SELECT Max(pk) form? Suppose another user creates a record in between the time when the first record is inserted and the second SQL select is issued?

I'd stick with the "official" MySQL format unless there was a very good reason not to.


This should not be a problem if I set isolation level setting as SERIALIZABLE.


Indeed - though that is a higher isolation level than is required - read committed will do.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic