[Chicago] auto incrementing - what options do we have?

[Lukasz Szybalski]

Hello,
I was wondering if anybody has a good strategy for auto incrementing fields?

I want to auto increment field called "case#" .

I have a choice of database auto increment on field "case#" or do it
myself? (correct? No other choices exists? or something in between?)

1. I would like to be able to do pick a number where we will start
doing a case#?  (1,000,000+)
2. Reserver a case# for a special group which can auto increment case#
between 2,000,000-2,999,999, and add them as they come.
3. I don't want to use (system_id)

So it seems as the only way is to make my primary key:
case# - unique key, primary, not auto incrementing and let some
program manage auto incrementing.

What options have worked for you in this situation?

So right now options are:
1. let db auto increment
2. Hold the next case# in a separate database table, and let my
program use it to find next case# value. How would I lock/unlock the
next case# to make sure there is no race condition and each case#, and
there is no holes?
3. Any other options?

Have people used other strategy that is semi-automatic, and would work
for these cases?

Thanks,
Lucas



-- 
Python and OpenOffice documents and templates
http://lucasmanual.com/mywiki/OpenOffice
Fast and Easy Backup solution with Bacula
http://lucasmanual.com/mywiki/Bacula