Manual Auto-Increment in Oracle without Trigger
Auto-increment is a useful feature in databases that allows for the automatic generation of unique numeric values for primary keys. In Oracle, this is typically achieved through the use of triggers. However, there are instances where the use of triggers may not be feasible or desirable. In this article, we will explore how to implement manual auto-increment in Oracle without the use of triggers.
Before we dive into the implementation, let's first understand the concept of auto-increment. Auto-increment is a feature that is commonly used to generate unique primary key values for each record in a database table. This ensures that each record has a distinct identifier, making it easier to retrieve and manipulate data. In Oracle, auto-increment is achieved by using a sequence object and a trigger. The sequence object generates a unique numeric value, and the trigger assigns this value to the primary key column in the table.
But what if you don't want to use triggers? Perhaps you want to avoid the overhead of creating and maintaining triggers, or maybe you are working on a legacy system where triggers are not allowed. In such cases, you can still achieve auto-increment functionality by using a combination of database objects and SQL statements.
The first step is to create a sequence object. This can be done using the CREATE SEQUENCE statement, specifying the starting value, increment value, and maximum value for the sequence. For example:
CREATE SEQUENCE seq_employee_id
START WITH 1
INCREMENT BY 1
MAXVALUE 999999999999999999999999999;
Next, we need to create a table to store the data. In this example, we will create an employee table with two columns: employee_id and employee_name. The employee_id column will be our primary key, and we will not specify a value for it during insertion.
CREATE TABLE employee (
employee_id NUMBER,
employee_name VARCHAR2(50)
);
Now, we need to insert data into the employee table. Since we have not specified a value for the employee_id column, it will be assigned the next available value from the sequence object. This can be done using the NEXTVAL function.
INSERT INTO employee (employee_name)
VALUES ('John Smith'),
('Jane Doe'),
('Michael Brown');
If we check the contents of the employee table, we can see that the employee_id column has been populated with unique values from the sequence object.
SELECT * FROM employee;
EMPLOYEE_ID EMPLOYEE_NAME
1 John Smith
2 Jane Doe
3 Michael Brown
So far, we have achieved auto-increment functionality without using triggers. But what about updating and deleting records? How do we ensure that the employee_id values remain unique?
To handle updates, we can use the CREATE OR REPLACE TRIGGER statement. This will create a trigger that will be executed before an update operation on the employee table. The trigger will check if the employee_id column is being updated and, if so, will raise an error. This will prevent any changes to the employee_id column, ensuring that the values remain unique.
CREATE OR REPLACE TRIGGER trg_employee_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
IF UPDATING ('EMPLOYEE_ID') THEN
RAISE_APPLICATION_ERROR(-20000, 'Employee ID cannot be updated');
END IF;
END;
/
For deletions, we can use the DELETE CASCADE option when creating the employee table. This will ensure that when a record is
