Re: sequence question

It will return 10000 and the insert statement will fail: The sequence is just a dumb number generator and has no idea what it is going to be used for. It has no communication with MYTABLE.PK. The first use of MYSEQ.NEXTVAL will return 10000 and the insert statement will fail if there is already a value of 10000 on a unique key field. You could use a trigger to automatically increment the sequence until a valid value is found, but it won't happen automatically.

Aaron Combs
Borland Software

Sudarshan Sampath wrote:

> This question is more to clarify the concept of a sequence.
>
> Say I have a table MYTABLE with column (PK NUMBER(10)) filled with
> rows in the range 10000 - 20000.
>
> I have a sequence that I create with the following params:
>
> Start With = 10000
> Increment by = 1
> Cache = 20
> Min Value = 10000
> Max Value = None
>
> Let's call this sequence MYSEQ.
>
> Now, if I referenced this sequence in my application with (MYSEQ.NEXTVAL) to
> insert
> a row in the table MYTABLE, would it give me a value 20001?
>
> insert into MYTABLE (PK)
> values
> (MYSEQ.NEXTVAL);
>
> Would this insert a new row with PK = 20001? Or would it give me an error
> till the sequence value
> is incremented until 20001? Does a primary key implicitly mean that the
> sequence we use against it
> will return a unique value for that table?
>
> I am merely trying to understand how the sequence works.
>
> Thanks,
>
> -Sudarshan
  Received on Thu Jul 19 2001 - 18:15:15 CDT