The SEQUENCE with DEFAULT functionality in Oracle 12c and later versions makes auto-incrementing ID generation easier and doesn’t require a trigger. In this blog, let us explore the SEQUENCE with DEFAULT to create an ID with AUTO_INCREMENT on Oracle.
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 on 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:
Explanation: Here, the Product_ID Column automatically gets unique values starting from 100, increasing by 10 for each new record.
Conclusion
Using a SEQUENCE with DEFAULT is a quick and easy way to create auto-incrementing primary key values in Oracle. By using sequence properties like START WITH, INCREMENT BY, and CACHE, it maintains control over ID generation while doing away with the requirement for triggers. Ensuring unique and consecutive ID assignments, this approach allows human inputs if needed. Applications that need complex structured ID generation will find it especially helpful. For handling auto-incrementing IDs in Oracle databases, SEQUENCE with DEFAULT is an all-around dependable and cutting-edge solution.
Other Methods for Creating an ID with AUTO_INCREMENT on Oracle
- Using a SEQUENCE and TRIGGER on Oracle SQL
- Using an IDENTITY Column on Oracle
- Using a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL
- Using a GUID on Oracle SQL
- Using a SEQUENCE with an INSERT Statement Directly on Oracle SQL
- Using a Combination of SEQUENCE and GUID on Oracle SQL
- Using a BEFORE INSERT TRIGGER with Custom Logic on Oracle SQL