Re: How to Query Min PK Value NOT in Table

simon wrote:
> I have a table with primary key values:
>
> 1, 2, 6, 8, 10, 13, 15,...
>
> Suppose I need to insert a new row with the minimum PK not used (here it is
> 3).
>
> Qn 1) What query should I use to get this min value? Assuming a single user
> environment.
>
> Qn 2) How could I handle the same query in multi-user environment where >1
> users may simultaneously get the minimum PK available is 3 and all try to
> insert 3? (recall that in Oracle reader never get blocked by writer, so
> simple locking mechanism doesnt prevent >1 users reading the value 3)

The simple answer is that filling in missing numbers is an irrelevancy serving no useful purpose to anyone or anything: So don't. A surrogate key must be unique. There is no business requirement anywhere that they be sequential from the standpoint of filling them in after the fact.

Think differently.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)