By using sequences, you can generate unique numbers that can be used for primary key values and other purposes.
A sequence generates a series of values by starting at the initial value and proceeding in increment steps.
A sequence is created with an initial value, an increment and a maximum value. A sequence may either be unique or non-unique. If a unique sequence reaches the maximum defined value, it becomes exhausted, whereas a non-unique sequence will start over from the initial value.
You can get the current value of a sequence, which is maintained on a connection basis. This makes it very easy to get the primary key value for the latest record inserted and use this value when inserting a record as a foreign key column in a referencing table.
Create Sequence You create a sequence using the create sequence statement, which has the following syntax
The default value for INITIAL_VALUE and INCREMENT is 1.
The default for MAX_VALUE is the largest value for an integer, i.e. 2,147,483,647. As an example, consider the following statement:
The sequence above gives the values 1,3,5,7,9,11,2,4,6,8,10,1,3 and so on indefinitely.
Then take a look at the following statement and note the difference:
This sequence will give the sequence 1,3,5,7,9,11,2,4,6,8,10 and then stop after this.
The use of a sequence in an SQL-statement is restricted to the creator of the sequence, but the creator may grant or revoke usage on the sequence to other idents in the database.
Drop Sequence When a sequence is dropped, all the objects (i.e. constraints, domains, functions, procedures, default values, triggers and views) referencing the sequence are also dropped. Drop the CUSTOMER_ID_SEQ sequence as follows:
Alter Sequence The creator of a sequence may alter it. The restart value must be within the limits of the min and max values for the sequence. In the following example the sequence is restarted with value 1:
To get values from a sequence, there are two constructs that can be used:
These can be used wherever an expression is possible, as in the following examples:
For further information on Sequences in Mimer SQL, please go to the common product documentation (see the SQL Reference Manual part):