corygm a écrit :

>If a user needs a "unique" identifier with meaning such as invoice
>number that encodes year and month plus an incremental number (i.e.
>YYYYMM######) then make it a separate column in the table with a
>unique index. You'll end up with an unchangeble primary key column
>(invoice_id -- created from a generator or other mechanism) and
>a "changeable, unique" identifier column (invoice_num). In essence
>you will have two unique identifiers for the data. One to manage the
>logical relationships between entities in your database and one to
>handle user interaction. The advantage is you can change the
>invoice_num data (and hence it's meaning) without affecting your
>logical relationships between entities in your database.
>
>

Quite right. I agree, that using a separate field with a UNIQUE
constaint may be better than using the PK directly. However, even then,
the problem is the same : if there's a UNIQUE constraint, the value
still has to be unique, and so it's still just as difficult to be _sure_
that the value is unique and that there are no gaps in the series. Hence
my idea of being able to have, basically, a hash table type of generator
that would maintain a separate counter for each value of the string
that's passed to it as a parameter. Doesn't this sound feasable? Doesn't
this sound useful?

Thanks!

Jonathan Neve.