How do I get the primary key of a record added to the database?
Many people use database tables with autonumber primary keys. These are columns (typically named "id") that the database will provide a value for by simply adding one to the last highest value. So if the last record inserted had an ID value of 5, the next will be 6. (Note that this isn't always true. You can't assume the next value will be one over the last highest value.) If you need to find out what value was used for the primary key, ColdFusion provides a simple way to do that.
To use this feature, you first must provide the result attribute to your cfquery tag. This tells ColdFusion to save information about the query to the variable named by the result attribute.
insert into people(name,email)
values("Paris Hilton", "trash@celebs.com")
</cfquery>
After running this query a structure named result will be created. Most of the keys of this structure are set, including the sql of the query, recordcount, and other values, however there is a special key that will store the value of the primary key assigned to the insertion. Unfortunately, this key value will vary depending on the database. For SQL Server, the value will be in the IDENTITYCOL key. For Oracle, the value will be in the ROWID key. For Sybase, the value will be in the SYB_IDENTITY key. For Informix, the value will be in the SERIAL_COL key. For MySQL, the value will be in the GENERATED_KEY key.
Using the above query as an example and assuming MySQL, you can display the primary key value like so:
This question was written by Raymond Camden.
It was last updated on July 15, 2008 at 10:06:58 AM EDT.
CFML Referenced
Categories
Comments
Comment made by todd sharp on July 15, 2008 at 11:02 AM
Just a note to readers that you can use the UDF getGeneratedKey at CFLib to normalize the key name returned using this method:
http://cflib.org/udf/getGeneratedKey
Comment made by Daniel Sellers on July 15, 2008 at 11:06 AM
In some instances the result.generated_key doesn't work. For my server setup I have to use result.identityCol which provides the same functionality.
Comment made by Raymond Camden on July 15, 2008 at 11:16 AM
Well could it be that you are on SQL Server? Generated_Key is only for MySQL.
Comment made by David Garthe on July 15, 2008 at 1:57 PM
For SQL Server, use the SCOPE_IDENTITY() method in your CFQUERY statment.
insert into people(name,email) values("Paris Hilton", "trash@celebs.com"); SELECT SCOPE_IDENTITY() as MyIdentity;
Then you can reference the identity value like "queryname.MyIdentiy".
Comment made by Raymond Camden on July 15, 2008 at 2:08 PM
David - while that works - I think it kinda defeats the purpose of this entry. Also, I'd probably recommend folks use the method described here as it makes their insert queries simpler.
Comment made by mike on July 15, 2008 at 3:17 PM
Isn't Ray's elegant solution only possible in Coldfusion 8?
Comment made by Raymond Camden on July 15, 2008 at 3:27 PM
The cookbook is meant to represent the most recent version of ColdFusion.
Comment made by Jared Shields on July 15, 2008 at 8:34 PM
Seems like nobody wants to leave the courteous comment... so I will:
Thanks for the always-helpful information Ray!
Comment made by Raymond Camden on July 15, 2008 at 9:35 PM
Heh, my ego isn't that weak. ;)
Comment made by Pat Dobson on July 16, 2008 at 3:31 AM
Brilliant !
I've been using all sorts of methods over the years to get the 'latest' primary key - this makes things much, much easier - Thank You !
Add a Comment