CFQUERY and autonumber primary keys

After reading a post by Ben Nadel, it occurred to me that I should write up my technique of getting new autogenerated primary key values back from the database.

It’s very common for database designers to use columns that generate a unique, artificial primary key value for every row inserted into a database table. Different platforms have different names for this type of column:

• Microsoft SQL Server: identity column property
• Microsoft Access: AutoNumber field type
• MySQL: AUTO_INCREMENT column property

PostgreSQL and Oracle use an alternative method called a sequence. Sequences are easier to use in some ways, and the technique I’ll describe below doesn’t really apply.

If you have child tables in your database related to your main table with Foreign Key constraints, you’re going to need to retrieve this autogenerated value before you can insert data into these child tables.

Let’s take a typical INSERT statement that pulls data from an HTML form POST operation:



INSERT INTO tblPeople (
nameLast,
nameFirst,
emailAddress
) VALUES (



)



Although INSERT CFQUERY tags don’t require a name attribute, I make sure to add one here. You’ll see why in a moment.

One handy property of the CFQUERY tag is that it can contain multiple SQL queries if you separate them with semicolons. You can use this trick to fetch the newly-created ID in this first query. This example works with MS SQL Server 2000 or higher:



INSERT INTO tblPeople (
nameLast,
nameFirst,
emailAddress
) VALUES (



);

SELECT SCOPE_IDENTITY() AS peopleID;



Because I named the CFQUERY instance, I can now reference the autogenerated ID value in my succeeding queries as:


#insertNewRecord.peopleID#


Don’t forget to wrap your entire batch of related CFQUERY tags inside a CFTRANSACTION tag to make them one complete operation.

One final note: The SCOPE_IDENTITY() function used above only works with SQL Server 2000 and higher. Anyone on SQL Server 7 (??) and before must use the special @@IDENTITY variable to accomplish the same thing. @@IDENTITY has some limitations with regards to databases with triggers, and may not retrieve the right value in all cases. Here’s a more thorough explanation of the problem.

People using MySQL can substitute the function LAST_INSERT_ID() to accomplish the same thing.