A sequence is a database object that users can use to generate unique integers. Unlike a serial data type that exists within a single table and is part of a transaction on that table, the sequence object is a separate object and can be used for multiple tables and is also independent of the transactions using it.
A sequence uses the INT8 range from –(263 -1) to 263-1.
CREATE SEQUENCE SQL statement is used to
create a sequence:
CREATE SEQUENCE sequence_1 INCREMENT BY 1 START WITH 0;
CREATE SEQUENCE statement has several
different clauses, only two of which are shown in the example in
Listing 30. The two shown allow for the capability to increment by
different values, as well as the number with which to start the
Some other clauses include capabilities like maximum value, minimum value, and cycling around to the minimum value after hitting the maximum value.
To get the current value of the sequence, use the
sequence.CURRVAL expression. To get the
next value in the sequence, use the
sequence.NEXTVAL expression. These
expressions can be used in
UPDATE SQL statements.
Listing 31. Example
CREATE SEQUENCE order_num_seq INCREMENT BY 1 START WITH 0; INSERT INTO orders ( order_num, SSN) VALUES (order_num_seq.NEXTVAL, "111-11-1111"); SELECT order_num_seq.CURRVAL, SSN FROM orders;