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

[Tejas Jain]

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]

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]

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
Select LAST_INSERT_ID()
At least, that's what I'm using.

[Tejas Jain]

Paul, Thanks for your suggestion.

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

[Paul Clapham]

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]

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]

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]

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]

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]

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]

If you are using any ORM framework, then you can get the Primary key immediately stored in the object after transaction commit

[Paul Sturrock]

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.