SQL SEQUENCE And NEXTVAL

Oracle uses the concept of SEQUENCE to create numerical primary key values as we add rows of data into a table. In Oracle, we first need to create the sequence. The syntax for creating a sequence is:

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 {Initial_Value} and {interval} are both 1.

Next, assume we have a table as follows:

TABLE USER_TABLE
(userid int,
last_name varchar(50),
first_name varchar(50));

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

CREATE SEQUENCE SEQ_USER START WITH 5 INCREMENT BY 5;

We will specify that we want to use the sequence and the NEXTVAL function in the INSERT INTO statement as follows:

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

userid	last_name	first_name
5	Washington	George
10	Jefferson	Thomas

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	last_name	first_name
15	Adams	John

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