I was recently developing a small application which I needed to be as database independent as possible. My goal was for the same code to run on top of the four most popular databases without having to change any SQL at all. The biggest problem I was running into was the fact that one of my component functions needed to insert a record, then return the ID of the record it just inserted. Most databases let you select the variable @@identity immediately following an insert statement, however not all. I decided to try to create a unique ID using ColdFusion’s createUUID() function, and use it as my primary key. The technique has worked out quite well. Below are some interesting points about the createUUID function:
- The algorithm that createUUID uses combines the time and date, the server’s IEEE 802 host ID and a random number, so theoretically, the numbers should be unique in all the world. I say “theoretically” because I have already seen people point out situations where MAC addresses might not be unique. But statistically speaking, they are pretty darn unique.
- There is really no significant performance overhead in generating the IDs. The first time you use createUUID, you might notice a pause as the software uses a JNI call to access your hardware configuration, however that value is cached so subsequent calls are fast.
- You are much more likely to run into performance issues when selecting data that uses a UUID for a primary key than inserting it simply because your primary key is a 35 digit string. For relatively small applications, you are not likely to notice any degradation, however this is not the right solution for enterprise level applications.
- If you want the convenience of generating your own primary key without the performance degradation of selecting from a table with a 35 character primary key, you can let your database create a normal auto-incrementing ID field as you normally would to use as your primary key, and insert a UUID at the same time. Then, just select the ID of the row that has the UUID that you just generated, and you are guaranteed (statistically) to have selected the right ID without having to use vendor specific SQL.
- You can always just turn around and select the max ID from the table that you just inserted data into (which means you don’t to generate a UUID at all), though you have to make sure that it is not possible that another thread could have inserted a record between the time you inserted your record and the time you selected the max ID. In other words, you have a potential race condition.