SQL SEQUENCE NEXTVAL

SQL > Advanced 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. 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

The syntax for creating a sequence in Oracle 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 value for {Initial_Value} and {interval} are both 1.

Example

Assume we have a table with the following structure:

Table USER_TABLE

Column Name	Data Type
Userid	integer
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 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

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.

Next: SQL Rank

This page was last updated on October 12, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.