CREATE SEQUENCE

Purpose

This statement is used to create a sequence. A sequence is a database object that generates unique integers.

If two users increment the same sequence simultaneously, each user may receive a sequence number with a gap, and one user will never receive the sequence number generated by the other user. Sequence numbers are generated independently of tables, so the same sequence can be used for one or more tables.

After a sequence is created, you can use the CURRVAL pseudocolumn to return the current value of the sequence or the NEXTVAL pseudocolumn to return the next incremented value in an SQL statement. For more information, see Sequence pseudocolumns.

Syntax

CREATE SEQUENCE [ IF NOT EXISTS ] [ schema. ] sequence_name
    [ INCREMENT BY int_value ]
    [ START WITH int_value ]
    [ MAXVALUE int_value | NOMAXVALUE ]
    [ MINVALUE int_value | NOMINVALUE ]
    [ CYCLE | NOCYCLE ]
    [ CACHE int_value | NOCACHE ]
    [ ORDER | NOORDER ]

Parameters

Examples

Create a sequence seq1 in the test schema and specify an increment step of 2. In the following example, the first reference to seq1.nextval returns 1, the second reference returns 3, and each subsequent reference returns a value that is 2 greater than the previous one.

obclient> CREATE SEQUENCE test.seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;

obclient> SELECT seq1.nextval FROM DUAL;

Expected return result:

+--------+
| nextval|
+--------+
| 1      |
+--------+
1 row in set

obclient> SELECT seq1.nextval FROM DUAL;

Expected return result:

+--------+
| nextval|
+--------+
| 3      |
+--------+
1 row in set