How to Create an AUTO_INCREMENT ID on Oracle?

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
  • Method 1: Using an IDENTITY Column on Oracle SQL
  • Method 2: Using a SEQUENCE and TRIGGER on Oracle SQL
  • Method 3: Using a SEQUENCE With DEFAULT on Oracle SQL
  • Method 4: Using a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL
  • Method 5: Using a GUID on Oracle SQL
  • Method 6: Using a SEQUENCE with INSERT Statement Directly on Oracle SQL
  • Method 7: Using a Combination of SEQUENCE and GUID on Oracle SQL
  • Method 8: Using a BEFORE INSERT TRIGGER with Custom Logic on Oracle SQL
• Performance Comparison of AUTO_INCREMENT Methods on Oracle SQL
• Real-world Examples
• Conclusion

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

Performance Comparison of AUTO_INCREMENT Methods on Oracle SQL

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