How to Use a Combination of SEQUENCE and GUID for Auto-Incrementing IDs in Oracle

In Oracle, unique and auto-incrementing IDs can be created by combining SEQUENCE with GUID (SYS_GUID). A SEQUENCE generates sequential numeric values, while SYS_GUID() generates globally unique identifiers (UUIDs). In this blog, let us explore the combination of SEQUENCE and GUID methods to create an ID with AUTO_INCREMENT on Oracle.

How to use a Combination of SEQUENCE and GUID?

SEQUENCE provides structured and incremental values while SYS_GUID() prevents duplication between different environments. The combination with GUID offers a well-balanced performance, uniqueness, and scalability. Very useful when integrating databases based on multiple systems. It is extensively used in applications that need both system-generated unique identities and IDs that are accessible by humans.

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;

Examples of using a combination of SEQUENCE and GUID on Oracle

Now, let’s see some examples for using a combination of SEQUENCE and GUID on Oracle for creating an ID with AUTO_INCREMENT.

Example 1:

--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

Example 2:

-- Create a sequence for auto-incrementing numeric IDs
CREATE SEQUENCE customer_seq
START WITH 100 
INCREMENT BY 1 
NOCYCLE NOCACHE;

-- Create a Customer table with SEQUENCE and GUID
CREATE TABLE Customers (
    Customer_id NUMBER DEFAULT customer_seq.NEXTVAL PRIMARY KEY,
    Customer_uuid RAW(16) DEFAULT SYS_GUID(),
    Customer_name NVARCHAR2(100),
    City NVARCHAR2(50)
);

-- Inserting data into the Customer table
INSERT INTO Customers (Customer_name, City) VALUES ('John Doe', 'New York');
INSERT INTO Customers (Customer_name, City) VALUES ('Jane Smith', 'Los Angeles');
INSERT INTO Customers (Customer_name, City) VALUES ('Robert Brown', 'Chicago');

-- Display data from the Customers table
SELECT Customer_id, RAWTOHEX(Customer_uuid) AS Customer_UUID FROM Customers;

Output:

Explanation: Here, the Customer_ID column gets automatically incremented by the customer_seq.NEXTVAL Statement. The RAWTOHEX() function converts the RAW(16) into a readable format in the SELECT Statement.

Conclusion

Combining SEQUENCE with GUID is a dependable and scalable method for obtaining AUTO_INCREMENT capabilities in Oracle. Sequential numbering methods, on the other hand, are associated with ordered numbers, while globally unique identifiers (UUIDs) are meant for unique identification across systems. SYS_GUID() helps developers avoid duplication in distributed contexts, and SEQUENCE features like START WITH and INCREMENT BY give them flexibility. It is especially helpful for apps that need to integrate with several databases. This method is still a strong and popular way to generate unique and auto-incrementing IDs in Oracle databases, even though it requires more storage for GUIDs.