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