Re: How to Query Min PK Value NOT in Table

> 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.
>

Perhaps let me rephase it with a real life example (this is imaginary, it may not be a good example, but i'm just trying to illustrate the need):

1. A table representing seat reservation of a cinema (perhaps let's assume the schema is existing and cannot be changed):

CREATE TABLE reservation (

    PK NUMBER primary key,
    seat_no NUMBER unique,
   blah_blah_blah....
);
2. Also, let's allow the PK to have no business meaning and will be generated from a sequence. So, the PK in my original post should be changed to some other unique key, say, seat_no, that represents a number corresponds to seat in the cinema. This number is not arbitrary and must within to a certain range.

3. Each row in the table represents a reservation for a seat_no. If the reservation is cancelled, the row is deleted or moved into another reservation log table. No matter how, in the existing schema, each row existing in the table must represent an existing reservation and any cancelled booking must mean the row deleted from this table.

4. As a result, seat_no can be of this sequence:

1, 2, 5, 8, ...

if previous reservation for seat_no 3, 6, 7 have been cancelled.

5. For group reservation, the system has to automatically allocate a set of adjacent seat_no's from the bottom or from a user-selected range. For example, to allocate 2 empty seats between seat_no 2,8 means we need to search the table in order to know 3,6,7 are the available seat_no between 2,8.

And, this is why I need to search for non-existing values - my business requirement is a bit different but hopes this illustrate why there could be a need to SELECT value not in the table. Received on Wed Jun 22 2005 - 10:57:30 CDT