Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Sequences in Mimer SQL
Categories: Database features, 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 proceeding in increment steps.
Description

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.

Function

Create Sequence
You create a sequence using the create sequence statement, which has the following syntax
CREATE [UNIQUE] SEQUENCE sequence_name 
[INITIAL_VALUE = integer_value]
[INCREMENT = integer_value]
[MAX_VALUE = integer_value]

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:
CREATE SEQUENCE infinite
INITIAL_VALUE = 1
INCREMENT = 2
MAX_VALUE = 11

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:
CREATE UNIQUE SEQUENCE finite
INITIAL_VALUE = 1
INCREMENT = 2
MAX_VALUE = 11

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:
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:
  1. next_value of sequence_name

  2. current_value of sequence_name

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

ALTER TABLE BOOK_GUEST ALTER RESERVATION
set default next_value of reservation_numbers

INSERT INTO BILL VALUES(current_value of 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 SQL Reference Manual part):



Last updated: 2009-02-24

 

Powered by Mimer SQL

Powered by Mimer SQL