SQL SEQUENCE NEXTVAL

Oracle uses the concept of SEQUENCE to create numerical primary key values as we add rows of data into a table. Whereas numerical primary key population for MySQL and SQL Server is tied to individual tables, in Oracle the SEQUENCE construct is created separately and is not tied to an individual table.

Syntax

CREATE SEQUENCE SEQUENCE_NAME 
[START WITH {Initial_Value}]
[INCREMENT BY {interval}];

{Initial_Value} is the starting value of the sequence, and {interval} is the interval between consecutive sequence numbers. Both [START WITH] and [INCREMENT BY] are optional fields. If they are not specified, the default value for {Initial_Value} and {interval} are both 1.

Example

Assume we have a table with the following structure:

Table USER_TABLE

and we want to use the following sequence to generate the userid:

CREATE SEQUENCE SEQ_USER START WITH 5 INCREMENT BY 5;

We specify that we want to use the sequence and the NEXTVAL function in the INSERT INTO statements in the following order:

INSERT INTO USER_TABLE VALUES (SEQ_USER.NEXTVAL, 'Washington', 'George');

INSERT INTO USER_TABLE VALUES (SEQ_USER.NEXTVAL, 'Jefferson', 'Thomas');

Now the table has the following two rows:

Table USER_TABLE

It is worth noting that a sequence is independent of a table. In other words, a sequence can be used to generate primary key values for multiple tables, and the sequence continues even if it is being applied to a different table. So, let's say for example we have a second table, Table NEW_USERS, which has the same structure as table USER_TABLE, and we issue the following SQL command after executing the two SQL commands above:

INSERT INTO NEW_USER VALUES (SEQ_USER.NEXTVAL, 'Adams', 'John');

Table NEW_USER will have the following row:

Table NEW_USER

Userid is 15 because that is the next value after 10.