> I will stop there as the rest is not relevant.
> YOUR example shows a generator?
> The generator will NEVER supply the same number to two different
> transactions, you would always get 5 and 6.
>

Of course not, but this is not my problem as I would not implement it
this way. The problem is that Stefan was attempting to avoid unique
constraints ever causing an exception by not inserting if he detected
a duplicate. The weakness in the model as I pointed out is that his
transaction does not see all the records in the table, and therefore
he can not run a query within the context of his transaction that
would identify the duplicate value under all circumstances. The
preceeding lookup although it would certainly see records committed
before his transaction started and could tell if he duplicated one of
those values, it could not categorically guarantee it was safe.

Of course if you use a GUID of some form, then you are never going to
have a problem with a primary key, BUT the important thing to remember
is that this is true of ANY unique constraint, not just PKs.

By the way, we do the exact same thing as you, creating a surrogate PK
instead of using the real data. I do not claim to have the only way or
the best fit solution to every problem in this approach. In my
experience, people tend to fail when they think a one size fits all
solution exists, and something that works well for one person will
automatically work well for everyone. The cache table is a good idea
if you are inserting data in high volume, but would obviously mean you
need to create a mechanism for dealing with the problem when it is
eventually processed.

Adam