• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 140
4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic