An independent database component that generates values in a specified sequence is known as a sequence. In this post, we will delve into the SEQUENCE alongside the INSERT Statement to establish an ID with AUTO_INCREMENT in Oracle.

How to implement SEQUENCE with INSERT in Oracle?

To directly utilize a sequence with an INSERT statement, you must reference sequence_name. To retrieve the next number in the sequence, employ NEXTVAL. This assures that every new record possesses a distinct ID that is auto-incremented. The unique feature of sequences is that they eliminate gaps in numbers across multiple user entries occurring simultaneously, making them frequently employed with TRIGGERs, sometimes incorporated within the INSERT statements for efficient ID management.

Syntax:

--We create an individual sequence and utilize 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 showcase the data 
SELECT * FROM table_name;

Illustrations of utilizing SEQUENCE with the INSERT Statement on Oracle

Let’s examine a few illustrations for using SEQUENCE with the INSERT Statement in Oracle to generate 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: The employee_seq generates distinct numeric IDs. The Employee_id isn’t auto-generated, thus allowing us to manually assign values through 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: In this case, the orders_seq begins at 1000 and increments by 1 for each new entry. The INSERT statement utilizes orders_seq.NEXTVAL to automatically generate distinctive order IDs.

Conclusion

A straightforward and effective approach to achieve AUTO_INCREMENT functionality in Oracle is by utilizing a SEQUENCE directly within an INSERT statement. It ensures sequential and unique ID generation without employing triggers. Developers maintain full oversight of ID management by configuring sequence attributes like START WITH, INCREMENT BY, and CACHE. This strategy is efficient in scenarios where numerous INSERT statements occur simultaneously. Even though it must be specifically referenced in INSERT statements, this method remains a well-liked and efficient solution for managing auto-incrementing primary keys in Oracle databases.

Alternative Methods for Creating an ID with AUTO_INCREMENT in Oracle

• Implementing a SEQUENCE along with a TRIGGER in Oracle SQL
• Utilizing a SEQUENCE with DEFAULT in Oracle SQL
• Utilizing a BEFORE INSERT Trigger with MAX(ID) in Oracle SQL
• Employing a GUID in Oracle SQL
• Utilizing an IDENTITY Column in Oracle SQL
• Employing a combination of SEQUENCE and GUID in Oracle SQL
• Using a BEFORE INSERT TRIGGER with Custom Logic in Oracle SQL

The article How to Use SEQUENCE with INSERT Statement for Auto-Incrementing IDs in Oracle first appeared on Intellipaat Blog.