Sequence AND Nextval IN SQL | Nextval in SQL | Sequence in SQL - sql - sql tutorial - learn sql




  • 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.
 process of sequence and nextval in sql

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

Example

  • Assume we have a table with the following structure:

Table USER_TABLE

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, 'Benzamin', '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 Benzamin 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.
  • 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', 'christ');
  • Table NEW_USER will have the following row:

Table NEW_USER

Userid Last_Name First_Name
15 Adams christ
  • Userid is 15 because that is the next value after 10.

Related Searches to Sequence AND Nextval IN SQL