At 5/22/2001 11:15 AM (Tuesday), Christopher P. Boothe wrote:

>[snipped excerpt from recommendation]
>It says at the end Still don't use document numbers for primary keys,
>though!
>
>Why would it say this. That's was my whole point of using these generators.
>I can probably live with losing a number here or there if I have too. But
>why would it say this about the generators. Is there another reason I
>should be careful?

Basically, the requirement that you maintain an unbroken series of unique
values is an application-level requirement, not a database-level
requirement. Most people see the application-level requirement and think
immediately that the same application-level ID values are natural
candidates for use as primary keys in the database. At first glance this
looks good. With experience you find out that this is not so good.

The database-level requirement for primary keys is that they be unique.
They do not need to be an unbroken series. In fact, the effort to keep them
an unbroken series is non-trivial and slows down the database processing.

Generators were invented to solve the needs of the database-level
requirement. Therefore, the generator mechanism, itself, does not deal with
"lost values" which result in a broken series. Trying to use generators to
achieve an unbroken series is perhaps the hardest path to follow.

What Jason, and others with lots of experience, are recommending is that
you use the generator to produce a primary key which is unique but not
guaranteed to be an unbroken series. This value is often hidden from the
user and is only used to maintain database integrity. Then, only if your
application needs it, use another field to hold the unbroken series ID
which your application needs. There are various methods you can use to
control those values which involve application code and triggers and tables
to hold values which have been "given back for reuse".