Auto_INCREMENT is important on Oracle for several key reasons especially when dealing with primary keys. Oracle achieves this using sequence and identity columns. In this blog, let us explore how we can create an ID with AUTO_INCREMENT on Oracle SQL.
Table of Contents:
Methods that can be used to do AUTO_INCREMENT on Oracle SQL
Before getting into the methods, let’s create an employee table that we will be using for all the examples.
CREATE TABLE Employees (
Employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
Method 1: Using an IDENTITY Column on Oracle SQL
We can define an IDENTITY column to generate unique values for the ID field automatically.
Syntax:
-- Creating a table with Ian IDENTITY Column
CREATE TABLE table_name (
column_name NUMBER GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ START WITH start_value ]
[ INCREMENT BY increment_value ]
[ MINVALUE min_value | NOMINVALUE ]
[ MAXVALUE max_value | NOMAXVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE cache_size | NOCACHE ]
PRIMARY KEY,
other_column_name column_type(size),
...
);
-- For inserting the data
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- To display data from the table
SELECT * FROM table_name;
Example:
-- Create the employees table with IDENTITY Column
CREATE TABLE Employees (
Employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
-- Inserting the data into the Employee table
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);
-- To display the data
SELECT * FROM Employees;
Output:
Explanation: We can use GENERATED AS IDENTITY to automatically generate unique values for the Employee_id.
Method 2: Using a SEQUENCE and TRIGGER on Oracle SQL
A sequence is an Oracle database SQL object that generates unique numeric values in order. It is used to simulate auto-increment.
Syntax:
-- Creating a table
CREATE TABLE table_name (
column_name NUMBER PRIMARY KEY,
other_column_name column_type(size),
...
);
-- Create 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 ];
-- Create a TRIGGER to AUTO-Assign ID
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
IF :NEW.column_name IS NULL THEN
SELECT sequence_name.NEXTVAL INTO :NEW.column_name FROM dual;
END IF;
END;
/
-- Insert the data into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- Display data from the table
Example:
-- Create the Employee table
CREATE TABLE Employees (
Employee_id NUMBER PRIMARY KEY,
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
-- Create a sequence for Employee_id
CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Create a trigger for Employee_id
CREATE OR REPLACE TRIGGER employees_before_insert
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
IF :NEW.Employee_id IS NULL THEN
SELECT employee_seq.NEXTVAL INTO :NEW.Employee_id FROM dual;
END IF;
END;
/
-- Inserting the data into Employees table
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);
-- Displaying the Employee table
SELECT * FROM Employees;
Output:
Explanation: The trigger automatically assigns the Employee_ID value from Employee_seq.
Method 3: Using a SEQUENCE With DEFAULT on Oracle SQL
This method leveraged a SEQUENCE in combination with a DEFAULT Keyword, eliminating the need for a trigger.
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;
Example:
--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)
);
--Inserting data into Employees table
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.
Method 4: Using a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL
This method generates an AUTO_INCREMENT ID by finding the maximum existing ID and adding 1.
Syntax:
--Create an Employee table
CREATE TABLE table_name (
column_name NUMBER PRIMARY KEY,
other_column_name column_type(size),
...
);
--Create a BEFORE INSERT Trigger
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
IF :NEW.column_name IS NULL THEN
SELECT COALESCE(MAX(column_name), 0) + 1 INTO :NEW.column_name FROM table_name;
END IF;
END;
/
--Insert the data into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
--To display the data
SELECT * FROM table_name;
Example:
-- Create an Employee table
CREATE TABLE Employees (
Employee_id NUMBER PRIMARY KEY,
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
--Create a BEFORE INSERT TRIGGER
CREATE OR REPLACE TRIGGER employees_before_insert
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
IF :NEW.Employee_id IS NULL THEN
SELECT COALESCE(MAX(Employee_id), 0) + 1 INTO :NEW.Employee_id FROM Employees;
END IF;
END;
/
--Insert some values
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: This TRIGGER automatically creates an EMPLOYEE_ID.
Method 5: Using a GUID on Oracle SQL
A GUID (Globally Unique Identifier) is a unique identifier used to ensure uniqueness across tables, databases, and distributed systems.
Syntax:
-- Create a table with GUID as the primary key
CREATE TABLE table_name (
column_name RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
other_column_name column_type(size),
...
);
-- Insert the data into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- To display the data
SELECT RAWTOHEX(column_name), other_column_name FROM table_name;
Example:
-- Create a table
CREATE TABLE Employees (
Employee_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
--Insert the values
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);
--To display the data
SELECT RAWTOHEX(Employee_id) AS Employee_ID, Employee_dept, Salary FROM Employees;
Output:
Explanation: Each row automatically gets a unique GUID called Employee_id. Since Employee_id is stored as RAW(16), we use RAWTOHEX(Employee_ID) to convert it into a readable format.
Method 6: Using a SEQUENCE with INSERT Statement Directly on Oracle SQL
A Sequence in Oracle SQL is a database object used to generate unique numeric values, typically for primary keys
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;
Example:
--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.
Method 7: Using a Combination of SEQUENCE and GUID on Oracle SQL
This method combines the numeric SEQUENCE-based ID with a GUID to ensure both local indexing efficiency and global uniqueness.
Syntax:
--Create a sequence
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY step_value
[optional parameters];
CREATE TABLE table_name (
column1 NUMBER DEFAULT sequence_name.NEXTVAL PRIMARY KEY,
column2 RAW(16) DEFAULT SYS_GUID(),
column3 column_type(size),
...
);
--Insert the data into a table
INSERT INTO table_name (column3, column4, ...)
VALUES (value3, value4, ...);
--To display the table
SELECT column1, RAWTOHEX(column2), column3 FROM table_name;
Example:
--Create an Employee table with SEQUENCE and GUID
CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1
NOCYCLE NOCACHE;
CREATE TABLE Employees (
Employee_id NUMBER DEFAULT employee_seq.NEXTVAL PRIMARY KEY,
Employee_uuid RAW(16) DEFAULT SYS_GUID(),
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
--Inserting data into a table
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);
--Display data from the employee table
SELECT RAWTOHEX(Employee_uuid) AS Employee_UUID, Salary FROM Employees;
Output:
Explanation: A SEQUENCE for numeric IDs (Employee_id). A GUID (RAW(16)) column for globally unique values
Method 8: Using a BEFORE INSERT TRIGGER with Custom Logic on Oracle SQL
This method uses a BEFORE INSERT TRIGGER to assign employee_id automatically based on the maximum existing Employee_id (MAX(Employee_id)) and next value from a sequence (employee_seq.NEXTVAL)
Syntax:
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY step_value
[optional parameters];
CREATE TABLE table_name (
column1 NUMBER PRIMARY KEY,
column2 column_type(size),
...
);
--Create BEFORE INSERT TRIGGER
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
IF :NEW.column1 IS NULL THEN
SELECT COALESCE(MAX(column1), sequence_name.NEXTVAL) + 1
INTO :NEW.column1
FROM table_name;
END IF;
END;
/
--Insert the data
INSERT INTO table_name (column2, column3, ...)
VALUES (value2, value3, ...);
--Display the table
SELECT * FROM table_name;
Example:
-- Step 1: Create a Sequence for Employee ID
CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1
NOCYCLE NOCACHE;
-- Step 2: Create the Employees Table
CREATE TABLE Employees (
Employee_id NUMBER PRIMARY KEY,
Employee_dept NVARCHAR2(50),
Salary DECIMAL(10,2)
);
-- Step 3: Create a BEFORE INSERT Trigger for Auto-Increment
CREATE OR REPLACE TRIGGER employees_before_insert
BEFORE INSERT ON Employees
FOR EACH ROW
DECLARE
v_max_id NUMBER;
BEGIN
-- Check the max Employee_id in case the sequence is behind
SELECT COALESCE(MAX(Employee_id), 0) INTO v_max_id FROM Employees;
-- Assign the next ID (greater of max ID or sequence nextval)
:NEW.Employee_id := GREATEST(v_max_id + 1, employee_seq.NEXTVAL);
END;
/
-- Step 4: Insert Data (Trigger will auto-generate Employee_id)
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);
-- Step 5: Display Data
SELECT * FROM Employees;
Output:
Explanation: A SEQUENCE is used to generate numeric IDs. A BEFORE INSERT TRIGGER sets the Employee_id
Method | Use case | Pros | Cons |
IDENTITY Column | When simple auto-increment is needed | No need for triggers or sequence | Gaps can occur if transactions are rolled back. However, gaps are minimized using NOCACHE and INCREMENT BY 1 |
SEQUENCE and TRIGGER | Good for legacy systems | Can handle manual ID overrides | Performance overhead due to extra PL/SQL execution |
SEQUENCE with DEFAULT | Best for Oracle 12c+ without needing triggers | Best performance after IDENTITY column | Requires manual handling of gaps |
BEFORE INSERT Trigger with MAX(ID) | Useful for small tables where avoiding ID gaps is important | Prevents sequence gaps | Can cause locking issues under high concurrency |
GUID | Best for distributed databases | Ensure uniqueness across multiple databases | Consumes more storage |
SEQUENCE with INSERT Statement Directly | Best when sequences are needed but no auto-increment enforcement | No triggers needed | Requires explicit use in every INSERT |
SEQUENCE + GUID | Best for Hybrid environments | Prevents ID collision in distributed systems | Extra storage space needed |
BEFORE INSERT Trigger with Custom Logic | For advanced use cases, a complex ID is needed. | Can implement custom rules | Custom logic can slow down inserts |
Real-world Examples
1. E-Commerce system: In an e-commerce system, we need to create a customer table where each customer gets a unique ID automatically.
Example:
-- Create a sequence
CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1;
-- Create the Customers table
CREATE TABLE CUSTOMERS (
CUSTOMER_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100),
EMAIL VARCHAR2(100) UNIQUE
);
-- Create a trigger to auto-increment CUSTOMER_ID
CREATE OR REPLACE TRIGGER customer_trigger
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
SELECT customer_seq.NEXTVAL INTO :NEW.CUSTOMER_ID FROM DUAL;
END;
/
INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Alice Johnson', '[email protected]');
INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Bob Smith', '[email protected]');
COMMIT;
--To display the table
SELECT CUSTOMER_ID,name FROM CUSTOMERS;
Output:
Explanation: Customer_seq is a sequence that starts with 1 and increases by 1 for each new row. This sequence will be used to generate unique customer_id values automatically.
2. HR System: In an HR system, we need to store employees with auto-incrementing employee IDs.
Example:
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
FULL_NAME VARCHAR2(100),
SALARY NUMBER(10,2)
);
--Insert some values into it
INSERT INTO EMPLOYEES (FULL_NAME, SALARY) VALUES ('John Doe', 50000);
INSERT INTO EMPLOYEES (FULL_NAME, SALARY) VALUES ('Jane Smith', 60000);
COMMIT;
--To display the table
SELECT EMPLOYEE_ID,salary FROM EMPLOYEES;
Output :
Explanation: The GENERATED ALWAYS AS IDENTITY statement makes Employee_ID auto-incrementing similar to AUTO_INCREMENT in MYSQL. Oracle SQL automatically generates unique sequential numbers for the columns.
3. Sales System: In the sales system, we track orders with an automatically generated ORDER_ID
Example:
-- Create a sequence
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
-- Create the Orders table
CREATE TABLE ORDERS (
ORDER_ID NUMBER PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(100),
ORDER_DATE DATE DEFAULT SYSDATE
);
-- Create a trigger to auto-increment ORDER_ID
CREATE OR REPLACE TRIGGER order_trigger
BEFORE INSERT ON ORDERS
FOR EACH ROW
BEGIN
SELECT order_seq.NEXTVAL INTO :NEW.ORDER_ID FROM DUAL;
END;
/
--Insert some data into the table
INSERT INTO ORDERS (CUSTOMER_NAME) VALUES ('Charlie Brown');
INSERT INTO ORDERS (CUSTOMER_NAME) VALUES ('Lucy Van Pelt');
COMMIT;
--To display the table
SELECT ORDER_ID,ORDER_DATE FROM ORDERS;
Output:
Explanation: Sequence order_seq: Creates a sequence named order_seq that will generate unique numbers. Each time the sequence is used, the next number will increase by 1.
Conclusion
You can create an ID with AUTO_INCREMENT on Oracle using sequence, identity columns, GUID, etc ., These methods increment the ID automatically based on the given conditions. Choose the method based on the requirements and use cases mentioned in the performance comparison table. Understanding these methods helps you to effectively AUTO_INCREMENT the ID column on Oracle.
FAQs
1. Does Oracle support AUTO_INCREMENT like MySQL?
No, Oracle uses SEQUENCE and TRIGGER instead of AUTO_INCREMENT for generating unique IDs.
2. How do I create an auto-incrementing ID in Oracle?
You create a SEQUENCE and use it in an INSERT statement or a BEFORE INSERT trigger.
3. Can I use a sequence without a trigger in Oracle?
Yes, you can directly use NEXTVAL from a sequence in an INSERT INTO statement.
4. How do I reset an Oracle sequence?
You need to drop and recreate the sequence or use ALTER SEQUENCE with a new START WITH value.
5. Can an Oracle sequence be shared across multiple tables?
Yes, a sequence is independent of tables and can be used for multiple tables if needed.