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

[Garrett Smith]

Auto-increment keys don't generally have meaningful values. If your various cases have meaning in your application (presuming they must if you want to include them in the schema), consider using another column to store case-specific data and let the auto-increment column just do its thing.

If you really want to hand craft the key values, the classic approach would be to use a table that holds your next key for each type. You need to read from that table, increment the applicable value and then write your new records within an isolated transaction (want read locks on everything you touch). Not the best approach for super high throughput applications, but unless you're planning on a wildly successful public facing web site, it should be fine.

It's common to use a stored procedure to increment the counter.

You can also use an abbreviated uuid, e.g. taking the last four or five characters, so your keys might look like a-89sj2, a-8s9sn, b-9sjnd, etc. These are more user friendly than true uuids. You'll need to anticipate key collisions in your application code and keep trying with new keys until the records make it into the table.


----- "Lukasz Szybalski" <szybalski at gmail.com> wrote:

> On Fri, Oct 10, 2008 at 8:58 AM, John-Stockton Irick
> <irick at uchicago.edu> wrote:
> > I'm going to assume that you're talking about mysql.
> >
> > >From mysql.org
> > "
> >  To start with an AUTO_INCREMENT value other than 1, you can set
> that
> > value with CREATE TABLE or ALTER TABLE, like this:
> >
> > mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
> > "
> >
> > http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
> >
> > Hope that helps.
> 
> 
> Problem with that is that I want to have more control on case#. So if
> this case is from A source the auto increment starts at case# 3000
> and
> if its from source B auto increment starts with case# 4000.
> 
> Lucas
> _______________________________________________
> Chicago mailing list
> Chicago at python.org
> http://mail.python.org/mailman/listinfo/chicago