How to Use GUID for Auto-Incrementing IDs in Oracle?

A unique, non-sequential identifier is provided for every row when an auto-incrementing ID is created in Oracle using a GUID (Globally Unique Identifier). In this blog, let us explore the GUID to create an ID with AUTO_INCREMENT on Oracle.

How to use a GUID on Oracle?

GUIDs help prevent ID prediction and improve security because they are not sequential. However, they may affect indexing performance and use more storage than numeric keys. GUIDs are still a solid option for applications that need globally unique identifiers.

Syntax:

CREATE TABLE table_name (
    column_name RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    other_column_name column_type(size),
    ...
);
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- To display the data 
SELECT RAWTOHEX(column_name), other_column_name FROM table_name;

Examples of using GUID on Oracle

Now, let’s see some examples for using GUID on Oracle for creating an ID with AUTO_INCREMENT.

Example 1:

-- 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: Hdre, 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.

Example 2:

-- Create the Customers table  
CREATE TABLE Customers (  
    Customer_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,  
    Customer_name NVARCHAR2(100),  
    Email VARCHAR2(255)  
);

-- Insert some values  
INSERT INTO Customers (Customer_name, Email) VALUES ('John Doe', '[email protected]');  
INSERT INTO Customers (Customer_name, Email) VALUES ('Alice Smith', '[email protected]');  
INSERT INTO Customers (Customer_name, Email) VALUES ('Robert Johnson', '[email protected]');  

-- Display the Customers table  
SELECT Customer_id FROM Customers;

Output:

Explanation: Here, the DEFAULT_SYS_GUID() function automatically generates a unique GUID for each of the inserted rows.

Conclusion

Using GUIDs, you may quickly create auto-incrementing primary key values in Oracle without the need for triggers or sequences. When maintaining uniqueness across several databases in remote systems, this approach is especially advantageous. However, because GUIDs are random, they can affect indexing performance and need extra storage. In addition, some queries may be slower with their non-sequential nature than with numeric keys. For applications that need globally unique identifiers in Oracle databases, GUIDs continue to be a dependable option despite these limitations.

Other Methods for Creating an ID with AUTO_INCREMENT on Oracle

• Using a SEQUENCE and TRIGGER on Oracle SQL
• Using a SEQUENCE With DEFAULT on Oracle SQL
• Using a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL
• Using a SEQUENCE with an INSERT Statement Directly on Oracle SQL
• Using an IDENTITY Column on Oracle SQL
• Using a BEFORE INSERT TRIGGER with Custom Logic on Oracle SQL
• Using a Combination of SEQUENCE and GUID on Oracle SQL