How to use SEQUENCE with DEFAULT for Auto-Incrementing IDs in Oracle [Oracle 12c+]

When working with Oracle databases, generating unique identifiers efficiently is crucial for maintaining data integrity. Oracle provides various methods for auto-increment primary key using sequence, especially with the enhancements introduced in Oracle 12c. One such approach is using SEQUENCE with DEFAULT, a clean and trigger-free method for auto-generated ID in Oracle database. In this article, we’ll learn about how to implement Oracle auto increment functionality using SEQUENCE and DEFAULT, with examples and comparisons to other techniques.

Table of Contents:

What is SEQUENCE with DEFAULT in Oracle?
How to use SEQUENCE with DEFAULT on Oracle?
Examples of using SEQUENCE with DEFAULT for Auto-Incrementing IDs in Oracle
Why Use SEQUENCE with DEFAULT?
Difference Between SEQUENCE with DEFAULT and IDENTITY Column
Conclusion

What is SEQUENCE with DEFAULT in Oracle?

In Oracle 12c and later versions, the SEQUENCE with DEFAULT functionality allows automatic generation of unique primary key values by assigning a sequence value as the default for a column. This method eliminates the need for triggers, enabling auto-incrementing IDs in a simpler and more efficient way. It combines the flexibility of sequences with the ease of default column values to replicate AUTO_INCREMENT behavior found in other databases.

How to use SEQUENCE with DEFAULT on Oracle?

When a new row is inserted, the database automatically assigns the next sequence value if a column is defined with the DEFAULT sequence_name.NEXTVAL. When necessary, manual value insertion is still possible using this less sophisticated approach. It makes use of sequence features like START WITH, INCREMENT BY, and CACHE to give users flexibility and control over ID generation. Oracle database performance and maintainability are improved by using SEQUENCE with DEFAULT.

Syntax:

-- Create a table
CREATE TABLE table_name (
    column_name NUMBER DEFAULT sequence_name.NEXTVAL PRIMARY KEY,
    other_column_name column_type(size),
    ...
);
-- For creating a SEQUENCE
CREATE SEQUENCE sequence_name 
START WITH start_value 
INCREMENT BY increment_value 
[ MINVALUE min_value | NOMINVALUE ] 
[ MAXVALUE max_value | NOMAXVALUE ] 
[ CYCLE | NOCYCLE ] 
[ CACHE cache_size | NOCACHE ];
--Inserting data into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- To display the data
SELECT * FROM table_name;

Examples of using SEQUENCE with DEFAULT for Auto-Incrementing IDs in Oracle

Now, let’s see some examples for using SEQUENCE with DEFAULT on Oracle for creating an ID with AUTO_INCREMENT.

Example 1:

--This sequence creates unique Employee_id values automatically
CREATE SEQUENCE employee_seq  
START WITH 1  
INCREMENT BY 1  
NOCYCLE  
NOCACHE;
-- Create the Employees' table 
CREATE TABLE Employees (
    Employee_id NUMBER DEFAULT employee_seq.NEXTVAL PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('BDT', 55000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TRA', 33000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TCW', 75000.00);
--To display the table 
SELECT * FROM Employees;

Output:

Explanation: The sequence creates unique EMPLOYEE_ID values automatically.

Example 2:

-- This sequence creates unique Product_id values automatically
CREATE SEQUENCE product_seq  
START WITH 100  
INCREMENT BY 10  
NOCYCLE  
NOCACHE;
-- Create the Product table  
CREATE TABLE Products (  
    Product_id NUMBER DEFAULT product_seq.NEXTVAL PRIMARY KEY,  
    Product_name NVARCHAR2(100),  
    Price DECIMAL(10,2)  
);
-- Inserting data into the Product table  
INSERT INTO Products (Product_name, Price) VALUES ('Laptop', 1200.50);  
INSERT INTO Products (Product_name, Price) VALUES ('Smartphone', 850.75);  
INSERT INTO Products (Product_name, Price) VALUES ('Headphones', 150.99);  
-- Display the Product table  
SELECT Product_id,Price from Products;

Output:

SEQUENCE with DEFAULT on Oracle example 2

Explanation: Here, the Product_ID Column automatically gets unique values starting from 100, increasing by 10 for each new record.

Why Use SEQUENCE with DEFAULT?

Simplifies schema by avoiding triggers.
Ensures ID generation is consistent and reliable.
Enhances maintainability in large-scale applications.
Supported natively since Oracle 12c auto increment.
Efficient for cases needing Oracle sequence primary key.

Difference Between SEQUENCE with DEFAULT and IDENTITY Column

Feature	SEQUENCE with DEFAULT	IDENTITY Column
Flexibility	High – full control over sequence properties	Limited configuration
Trigger Required	No	No
Version Introduced	Oracle 12c	Oracle 12c
Portability	More portable for custom logic	Less portable
Manual Insert	Yes, override is allowed	May need special settings

Conclusion

Using a SEQUENCE with DEFAULT is an efficient and modern way to implement Oracle auto increment functionality, especially in Oracle 12c and later. It helps achieve auto-generated ID in Oracle database systems without the complexity of triggers. Whether you’re working with employee records or product catalogs, this method ensures consistency and maintainability while enabling easy auto increment primary key using sequence in Oracle. It’s a strong alternative to the Oracle sequence primary key setup using older methods.

To learn more about SQL functions, check out this SQL Course and also explore SQL Interview Questions prepared by industry experts.