Sequences in Mimer SQL

Introduction

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 either increments or decrements the sequence value.

Description

A sequence is created with an initial value, an increment and a maximum or minimum value. A sequence may either be with CYCLE or NO CYCLE. If a sequence with NO CYCLE reaches the minimum or maximum defined value, it becomes exhausted, whereas a sequence with CYCLE will start over from the initial value. It possible to specify which data type the sequence should use, and either SMALLINT, INTEGER or BIGINT can be used. The data type specified will determine the limits for MINVALUE and MAXVALUE.

For a SMALLINT the sequence values will fall into the range -32768 and 32767,  INTEGER has the range -2147483648 to 2147483647 and for BIGINT the range is -9223372036854775808 to 9223372036854775807.

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.

Function

Create Sequence

You create a sequence using the create sequence statement, which has the following syntax:

 CREATE SEQUENCE sequence_name
 [AS datatype]
 [START WITH integer_value]
 [INCREMENT BY integer_value]
 [MAXVALUE integer_value | NO MAXVALUE]
 [MINVALUE integer_value | NO MINVALUE]
 [CYCLE | NO CYCLE]

The default value for INITIAL_VALUE and INCREMENT is 1.

The default for MAX_VALUE is the largest value for an integer, i.e. 2147483647. As an example, consider the following statement:

 CREATE SEQUENCE infinite
   START WITH 1
   INCREMENT BY 2
   MAXVALUE 11
   CYCLE

The sequence above gives the values 1, 3, 5, 7, 9, 11, 1, 3, 5, 7, 9, 11 and so on indefinitely.

Then take a look at the following statement and note the difference:

 CREATE SEQUENCE finite
   START WITH 0
   INCREMENT BY -1
   MINVALUE -5
   NO CYCLE

This sequence would give the values 0, -1, -2, -3, -4, -5 and would then be exhausted.

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, methods, triggers and views) referencing the sequence are also dropped with option CASCADE. Drop the CUSTOMER_ID_SEQ sequence as follows:

DROP SEQUENCE customer_id_seq CASCADE;

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:

ALTER SEQUENCE id_seq RESTART WITH 1;

Techniques

To get values from a sequence, there are two constructs that can be used:

• next value for sequence_name
• current value for sequence_name

These can be used wherever an expression is possible, as in the following examples:

 CREATE SEQUENCE reservation_numbers NO CYCLE;

 ALTER TABLE BOOK_GUEST ALTER RESERVATION
                set default next value for reservation_numbers;

 INSERT INTO BILL VALUES(current value for reservation_numbers,
                         localtimestamp(0), 100, 37.50);

Links

For further information on Sequences in Mimer SQL, please go to the common product documentation. See the Reference Manual part of the Mimer SQL Documentation set.