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.
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.
Read Also
Insert into select.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');
Read Also
Inline view in sql.- 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.