Necessity was very much the mother is this little invention. My conundrum was this: the application I was designing wrote the same data to two databases; one on our intranet and the other on the World Wide Web. I was using auto-increment to set my unique identifier for each record. But what if one of the writes failed to either of the databases? Would that not mess up the index of IDs? Would the subsequently written records be inconsistently identified?
I had neither the time nor the desire to reference the records by anything other than their unique identifiers (that’s what they’re there for after all!). So I decided to forego the whole auto-increment feature, and write my own IDs. So now I just had to work out some efficient way of getting/setting the highest ‘id’ within a table. Here’s the function I came up with:
'===============================
Function maxID( strTable, pk )
'===============================
Dim intMax
If pk = "" Then pk = "id"
strSQL = "SELECT MAX(" & pk & ") FROM " & strTable
intMax = getCount( "", "", strSQL )
If IsNull( intMax ) Then
intMax = 0
Else
intMax = cInt( intMax )
End If
maxID = intMax
End Function
Explanation
All this little function accomplishes is a retrieval of the highest primary key within a table. The primary key (pk) defaults to ‘id’, the column name used by probably 99% of all DBA’s. You’ll notice that I hotwired the getCount() function to retrieve the MAX id for us, which it does quite happily. Maybe you’re now thinking that I should change the name of getCount() to reflect the fact that it can get other things besides just counts. Well, I suppose the only reason that I haven’t, is because getCount() is easier to remember and faster to type then getCountOrPossiblySomeOtherRequiredValue().
Anyhow, we can easily use this maxID() to set our unique identifiers like this:
Dim intUser
intUser = maxID( “users”, “” ) + 1
‘=== first enter the user, assuming we’ve checked
‘=== to make sure they don’t already exist
strSQL = “INSERT INTO users ( id, email, name )
VALUES ( “ & _ intUser & “, ‘me@here.com’, ‘Me ’ )”
Call commitToDB( strSQL, “” )
‘=== now subscribe to the newsletter
strSQL = “INSERT INTO subscribed_to
(user_id, newsletter_id )
VALUES ( “ & _ intUser & “, 3 )”
Call commitToDB( strSQL, “” )
There we have it, we can our own unique ID, and easily keep it consistent across countless tables and databases. I would also recommend the use of foreign key constraints to ensure referential integrity, but that’s beyond this article.
It was interesting to discover after creating this technique that I had inadvertently complied with a ‘best-practice’ of the DBA world. It turns out that many people are highly critical of using auto-increment, no matter what the application/database requirements. In fact, you could read up on this raging debate if you like.
The other reason I highly recommend this method, is that down the road you may wish to upgrade/switch your database engine. It will be much easier to do when we don’t have to worry about the new system using auto-increment in exactly the same manner. That’s because we never used it, our SQL statements never depended on it, for all we care the database could suffer severe frostbite and have to undergo amputation of it’s auto-increment appendage. We simply refuse to have our great applications brought to their knees by a simple ID.
Next: Conclusion >>
More Database Articles
More By Justin Cook
