CREATE SEQUENCE Syntax

In Oracle, sequence is a database object that auto generates a number sequence. This is typically used in a primary key int column.

To create a sequence using the CREATE SEQUENCE statement:

CREATE SEQUENCE sequence_name
 [INCREMENT BY interval]
 [MINVALUE min_value | NOMINVALUE]
 [MAXVALUE max_value | NOMAXVALUE]
 [START WITH start_value]
 [CYCLE | NOCYCLE]
 [CACHE cache_size | NOCACHE]
 [ORDER | NOORDER]
;
  • The INCREMENT BY clause defines the gap between two consecutive values of the sequence.

By default, it’s 1. If it’s set to be positive, the sequence is ascending e.g., 1,2,3,… If it is negative, the sequence is descending e.g., 9,8,7…

  • The START WITH clause defines the first value for the first row created.

  • When you use CYCLE, the sequence will reset when it reaches the limit.

  • The CACHE clause specifies the number of sequence values to be cached for better performance.

  • When you omit the MAXVALUE, it’s set to the default value 999999999999999999999999999

When adding records to the data table, the sequence auto-populates the value so you don’t have to input manually for that column. You retrieve the value for a new record using NEXTVAL:

sequence_name.NEXTVAL

Example

To create a new sequence:

CREATE SEQUENCE student_seq
 MINVALUE 1
 MAXVALUE 100000
 START WITH 1
 INCREMENT BY 1
 CACHE 20;

And when you insert a new row to the table, use NEXTVAL to retrieve the next value:

INSERT INTO student
(student_id, student_name)
VALUES
(student_seq.NEXTVAL, 'John Snow');

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode