aspose file tools*
The moose likes JDBC and the fly likes Is there a way to get the primary key with just one insert statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is there a way to get the primary key with just one insert statement?" Watch "Is there a way to get the primary key with just one insert statement?" New topic
Author

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

Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
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.


"Knowing is not enough, you must apply... Willing is not enough, you must do."
--Bruce Lee
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30917
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18886
    
    8

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

Joined: Mar 04, 2008
Posts: 119
Paul, Thanks for your suggestion.

At which point, do you think "select max(pk) from table_name" will break?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18886
    
    8

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

Joined: Mar 04, 2008
Posts: 119
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
Bartender

Joined: Oct 14, 2005
Posts: 18886
    
    8

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

Joined: Sep 01, 2010
Posts: 115
    
    1
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
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

Joined: Dec 14, 2009
Posts: 133
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

Joined: Apr 14, 2004
Posts: 10336

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Is there a way to get the primary key with just one insert statement?