How to Use SEQUENCE with INSERT Statement for Auto-Incrementing IDs in Oracle

An autonomous database entity that produces numbers in a predetermined order is called a sequence. In this blog, let us explore the SEQUENCE with the INSERT Statement method to create an ID with AUTO_INCREMENT on Oracle.

How to use SEQUENCE with INSERT in Oracle?

You must call sequence_name to use a sequence directly with an INSERT statement. To obtain the following number in the sequence, use NEXTVAL. This guarantees that each new row has a unique ID that is automatically increased. The fact that the sequences exclude the gaps in numbers across the multiple entries of users, which are occurring simultaneously, ensures that they are used frequently with TRIGGERs, sometimes as part of the INSERT statements for effective management of IDs.

Syntax:

--We create a separate sequence and use it manually in the INSERT Statements
CREATE SEQUENCE sequence_name
START WITH initial_value  
INCREMENT BY step_value  
[optional parameters];  

CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);

--Inserting data using SEQUENCE
INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (sequence_name.NEXTVAL, value2, value3, ...);
--To display the data 
SELECT * FROM table_name;

Examples of using SEQUENCE with the INSERT Statement on Oracle

Now, let’s see some examples for using SEQUENCE with the INSERT Statement on Oracle for creating an ID with AUTO_INCREMENT

Example 1:

--To create a separate sequence
CREATE SEQUENCE employee_seq
START WITH 1 
INCREMENT BY 1 
NOCYCLE NOCACHE;

CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);
--Insert into the table
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'HR', 50000.00);
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'Finance', 60000.50);
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'IT', 75000.75);
--To display the table
SELECT * FROM Employees;

Output:

Explanation: Employee_seq generates unique numeric IDs. The Employee_id is not auto-generated, so we can manually assign values using the sequence.

Example 2:

-- Create a sequence for the orders table
CREATE SEQUENCE orders_seq
START WITH 1000
INCREMENT BY 1
NOCYCLE NOCACHE;

-- Create the Orders table
CREATE TABLE Orders (
    Order_id NUMBER PRIMARY KEY,
    Customer_name NVARCHAR2(100),
    Order_amount DECIMAL(10,2)
);

-- Insert records using the sequence
INSERT INTO Orders (Order_id, Customer_name, Order_amount)  
VALUES (orders_seq.NEXTVAL, 'John Doe', 250.75);

INSERT INTO Orders (Order_id, Customer_name, Order_amount)  
VALUES (orders_seq.NEXTVAL, 'Jane Smith', 400.50);

INSERT INTO Orders (Order_id, Customer_name, Order_amount)  
VALUES (orders_seq.NEXTVAL, 'Michael Brown', 150.25);

-- Display the table data
SELECT Order_id,Order_amount FROM Orders;

Output:

Explanation: Here, the orders_seq starts from 1000 and increases by 1 for every inserted row. The INSERT statement uses orders_seq.NEXTVAL to auto-generate unique order IDs.

Conclusion

A straightforward and efficient method of achieving AUTO_INCREMENT capabilities in Oracle is to use a SEQUENCE with an INSERT statement directly. Without the use of triggers, it guarantees sequential and unique ID generation. Developers have complete control over ID management by setting sequence attributes like START WITH, INCREMENT BY, and CACHE. This method performs well where several INSERT statements take place at once. It is still a popular and effective way to deal with auto-incrementing primary keys in Oracle databases, even though it needs to be used explicitly in INSERT statements.