At 9/6/2003 02:11 PM (Saturday), sugi wrote:

>Now here's some crazy idea:
>What if we just use one generator for all table PKs in a database?

Not so crazy at all. Go for it!

I have done this on several databases. The only issue I see is whether your
usage will likely cause the values to increase past the limit of the
generator. Some simple estimating will probably convince you that this is
highly unlikely.

I also wrote some code to query each table which uses a generated PK to
determine the highest value already used in the database. I will use this
to reset the generator's current value in case of database corruption or
other problems which make me lose confidence in the situation. I would
recommend you prepare something similar. AFter all, you don't want the
generator to start re-using values for some indeterminate amount of time
before you stop it!

Finally, a nit-pick on "enterprise-unique": I would not consider these
values as enterprise-unique, but only server-unique. I would expect an
enterprise to maintain several, if not many, separate databases on
different servers. So, the addition of a server-ID, or database-ID, would
be needed to make the values enterprise-unique.