I know there are a lot of tutorials that use this technique. I also know there's a lot of inherited code (in any language) that contains this SQL. I also also know that - years ago - I too was a slave to SELECT MAX(id). But this is the 21st century and you need to stop using it.

The SQL examples shown here comes from part 7.3 of my OOP with ColdFusion Primer.

The Scenario

1. Insert record into database table.

2. Retrieve the ID of the record we just inserted

3. Insert record into a related table using the ID we just retrieved to relate it

The Problem

Odds are, if you're using SELECT MAX(id), you're also NOT running these related queries within a transaction. It's also very possible you're not even using a database that is capable of using transactions.

When you're running this code on your local development box, you won't ever see a problem with this code. If your application is only ever used by a few users, most likely you'll never have an issue. It's when your user base increases and the number of concurrent requests to the database start to stack up, the MAX(id) you expect will not be the MAX(id) you get back.

Multiple threads and concurrent requests

Picture the gaps between the cfquery tags in each step. Now picture that the queries are cars driving in the same lane down the highway. That lane is your request for a new record in CONTACTS, the new Contact's ID and the association of that Contact with its related Categories in CONTACT_CATEGORIES.

Now picture there's a lane next to you with another set of cars. That's someone else's Contact creation request. Each lane can only handle so many cars and each individual car runs at a different speed. What happens when all the lanes are full and there are cars ahead and behind you?

You know some jerk is going to swerve in between the cars in the next lane just to try and get where he's going just a little faster.

At some point, a car from another lane is going to jump ahead of or behind the car containing the "SELECT MAX(id)" that should be in your request. The car could be the other lane's INSERT (car1) or "SELECT MAX(id)" (Car2). It's even possible that a car on your left and a car on your right jump into your lane at the same time. Now you're really in trouble.

You have to write your queries so that not only do your cars follow each other in a certain order, you also have to make sure that you use a process that retrieves your new CONTACT_ID and blocks cars in another lane from jumping into yours.

The Solutions

Most modern databases have built-in functionality to allow you to retrieve the most recently created primary key in any table. Depending on your needs, you can even safely create your own primary key.

Here are three common approaches to getting the most recently created Primary Key of a table:

1. Auto-incrementing Primary Key functions

Tables in these databases can be defined to automatically create a new Primary Key as each record is created.

MS-Access

First off, here's Nine Reasons NOT To Use MS Access To Power A DB-Driven Website. But if you absolutely MUST use it, Bruce Johnson has some code here that will safely get the last inserted ID.

SQL Server through version 7 *

Pretty much the only thing you can use here is @@IDENTITY

SQL Server 2000 through current *

You have three choices here.

1. @@IDENTITY: This is an intrinsic variable in SQL Server that contains the Id of the record that was last created on this connection, regardless of the table. (See code above.)

The problem with @@IDENTITY is that if you do an INSERT to TableA and you also have a Trigger that then causes an INSERT to TableB, then the value of @@IDENTITY shifts from the newest ID in TableA to the newest ID in TableB.

2. IDENT_CURRENT('table'): This function returns the ID of the record that was last created in the specified table.

The issue here is that it's possible by the time you call IDENT_CURRENT('CONTACTS') from your request, another request has inserted a record in that table.

3. SCOPE_IDENTITY(): This variable contains the Id of the last record that was created within the current scope, regardless of the table.

This seems to be the safest as it disregards IDs created by Triggers or some other process and sticks to the ID created by your connection's INSERT.

* Some info on MS-Access and SQL Server quoted or paraphrased from this post by Bruce Johnson.

MySQL

LAST_INSERT_ID(): From the documentation - "For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed."

As a matter of fact, you could even eliminate Step 2 and just use Steps 1 and 3 in this manner:

You could do that same for SQL Server by replacing LAST_INSERT_ID() with SCOPE_IDENTITY().

DB2

IDENTITY_VAL_LOCAL(): If your table has been created using an IDENTITY column for its Primary Key, then you can retrieve a new ID using this function.

2. Sequences

Tables in these databases use associated objects called SEQUENCES to manage ID values for their Primary Keys. Sequences don't necessarily increment by 1 like auto-generated keys. You can also manipulate a sequence on the fly, but that's another topic. The point is that there are functions which allow you to access the value of a new record's ID when that ID is based on an associated sequence.

DB2

Some versions of DB2 can use IDENTITY, some can't. Those that can't, use SEQUENCES. Using the generic Sequence, here's how we'd change the INSERT Statement in Step 1:

This would create a record with CONTACT_ID = 1 since this would be the first record in the table and we started the sequence at 1. So now we need to get the current value of the sequence to associate this new Contact to its Categories.

"The PREVIOUS VALUE expression returns the most recently generated value for the specified sequence for a previous statement within the current application process."

Documentation for Sequences on DB2.

Oracle

Oracle uses the same generic SQL to create a Sequence, but it has a few differences on its attributes. Check the documentation for more info. Sequences work the same with Oracle as they do with DB2, the difference is that Oracle uses a dot syntax to perform operations on them.

Both accomplish the same thing: they generate the next value of the sequence and pass it to some SQL operation. Getting the current value of the sequence makes more sense in Oracle than it does in DB2.

Again, we could remove Step 2 and jump straight to Step 3

Documentation for Sequences on Oracle.

PostgreSQL

PostgreSQL uses the same generic SQL to create a Sequence, differences in attributes are in the documentation. We can use Sequences in PostgreSQL just as we do with DB2 or Oracle, but PostgreSQL opted to use a function-based syntax. You can also run into case sensitivity issues, so check the documentation for those.

But at least they go with Oracle's more sensible approach to getting a sequence's current value:

Documentation for Sequences on PostgreSQL.

Summary

Get the next (new) value of a sequence (Primary Key):

DB2:

Oracle:

PostgreSQL:

Get the current value of a sequence (Primary Key):

DB2:

Oracle:

PostgreSQL:

3. User Defined Value

The final option can be used with any database. The downside is that these keys are usually going to be of a string data type and that could have some impact on indexing and data look ups as your data set grows.

Rather than create some arbitrary string of characters on your own. You would most often opt to use what's called a UUID, a universally Unique Identifier.

ColdFusion, for example, has a function called CreateUUID() as of version 4.01 that creates a unique 35 character ID. Many other programming languages have similar functions.

So here are our SQL queries using our own User Defined Value for a CONTACT_ID:

Since we already know the value of the new Contact ID, there's no need for Step 2.

Summary

The examples shown here use separate queries for each step to walk you through the required process for each database. Certainly you can combine these queries under a single connection (single CFQUERY) by separating them with a semi-colon ( ; ).

Even with separate connections for each step, you'll want to wrap the entire process in a single database transaction to ensure you're getting the correct new ID and to further insulate this series of related queries from those of another request. You'll also want to add in a try/catch block to roll back the transaction in case anything goes wrong. If you're unfamiliar with CFTRANSACTION, CFTRY and CFCATCH, you can read about them here.

Hopefully, this has given you insight on parts of a database that may have gone unseen until now. Trust me, if you poke around a bit, you'll find more and more reasons to code directly in your database instead of in your application. Getting the newest Primary Key value is only the tip of the iceberg.