Martin Vajsar wrote:You should definitely use autoincrement (or a sequence in databases that use them instead of autoincrement). It is scalable, robust, and most portable way of doing it.
On the other hand, obtaining current maximum is non-scalable (requires some sort of locking), less effective (requires one more roundtrip to the database), fragile and not readily portable (in some databases you need to use correct isolation level, in other databases, you even need to explicitly lock to avoid duplicates).
Martin Vajsar wrote:You don't.
The sole purpose of a primary key is to uniquely identify a row. In some rare cases, you can use a natural primary key (that is, you'll use a key which has a specific meaning as a primary key), but only if you're really sure it is immutable. More often than not, the users will eventually find out they need to modify the primary key (just in this exceptional case, of course), and the can of worms of mutable primary keys is open - good luck. Using record IDs as primary keys is one of the usual ways to mutable primary keys.
![]()
If you need to generate some sort of a record ID, you can do it "on the fly" by storing individual parts of the key in the table (or even in other related tables) and just concatenating them to form the desired record ID.
And one more note: sequences and autoincrement IDs will have gaps (skipped numbers). There isn't a practical way to generate gapless sequences. Every at least slightly scalable gapless generation scheme can be thwarted by a rollback. Every possible scheme can be defeated by deletions.
James Boswell wrote:what database are you using? MySQL?
Jacky Luk wrote:Should I "store" this transient transaction ids? for easy lookups?
Say the employee really wants to look up an id named 0001-20130821-A10, so should I just break down the request and gather the information as a final piece?
No more Blub for me, thank you, Vicar.
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime. |