In Oracle, distinctive and auto-incrementing identifiers can be established by merging SEQUENCE with GUID (SYS_GUID). A SEQUENCE produces sequential numeric values, while SYS_GUID() creates globally unique identifiers (UUIDs). In this article, we will delve into the integration of SEQUENCE and GUID methods to generate an ID with AUTO_INCREMENT in Oracle.

How to Utilize a Combination of SEQUENCE and GUID?

SEQUENCE offers organized and incremental values whereas SYS_GUID() ensures no duplication across various environments. The integration with GUID delivers a balanced performance, distinctiveness, and scalability. This is particularly beneficial when synchronizing databases from multiple systems. It’s widely employed in applications requiring both system-generated unique identities and human-readable IDs.

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;

Illustrations of Utilizing SEQUENCE and GUID Together in Oracle

Now, let’s review some illustrations for employing a combination of SEQUENCE and GUID on Oracle to create 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 identifiers (Employee_id) and 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: In this case, the Customer_ID column is automatically incremented by the customer_seq.NEXTVAL statement. The RAWTOHEX() function translates the RAW(16) into a recognizable format in the SELECT statement.

Conclusion

The combination of SEQUENCE and GUID provides a reliable and scalable approach for achieving AUTO_INCREMENT functionality in Oracle. Sequential numbering techniques yield ordered numbers, whereas globally unique identifiers (UUIDs) are formulated for unique identification across systems. SYS_GUID() assists developers in preventing duplication in distributed scenarios, while SEQUENCE attributes like START WITH and INCREMENT BY offer flexibility. This methodology is especially advantageous for applications needing to integrate with various databases. Despite requiring additional storage for GUIDs, this technique remains a robust and favored means to produce unique and auto-incrementing IDs in Oracle databases.

Alternative Methods for Generating an ID with AUTO_INCREMENT in Oracle

• Utilizing a SEQUENCE and TRIGGER in Oracle SQL
• Employing a SEQUENCE With DEFAULT in Oracle SQL
• Implementing a BEFORE INSERT Trigger with MAX(ID) in Oracle SQL
• Applying a GUID in Oracle SQL
• Using a SEQUENCE with an INSERT Statement Directly in Oracle SQL
• Leveraging an IDENTITY Column in Oracle
• Utilizing a BEFORE INSERT TRIGGER with Custom Logic in Oracle SQL

The article How to Use a Combination of SEQUENCE and GUID for Auto-Incrementing IDs in Oracle first appeared on Intellipaat Blog.